How do I change the information that's in autofill?

Using autofill when referencing subtotals

• Hi Craig! I am struggling to repeat an action inthat I did last year (I make this report once per year). My spreadsheet has two worksheets, "Weekly" and "Data". In the Data worksheet, I have a row for each day of the year and a variety of columns with activity names such as "Solicitations Out" and "Contracts closed. Each day my sales managers enter the number they performed each activity. The information is subtotaled by week, and so when the new activity counts are entered, they also update the subtotal row. On the Weekly worksheet, for each activity I have three columns; "Goal" (which never changes), "Actual" (which references the subtotal row), and "%" (which is actual/goal). My problem is that I want to autofill (or use some Ctrl+Enter type command) to enter the reference to the subtotals on the Data worksheet so that I don't have to go in and manually link up 106 cells (two years) per column. I cannot get it to fill in the formula correctly, skipping eight cells at a time. For example, my desired return on the Weekly sheet would be =Data!B5 =Data!B13 =Data!B21 =Data!B29 When I use Autofill, I get =Data!B5 =Data!B6 =Data!B7 If I enter =Data!B5 and =Data!B13 and Autofill with the first two columns highlighted, I get =Data!B5 =Data!B13 =Data!B7 =Data!B15 =Data!B9 I've tried every combination of Autofill that I can with the subtotals collapsed. I can't seem to figure out how to apply visible cells only when I want to refer to the subtotal. It's important that this isn't a copy and paste, because this document is constantly being updated (i.e. the subtotals change on a daily basis). Thank you so much for your help and if you need me to send you the spreadsheet, let me know. The image I've attached shows what I would like the outcome to be (I got here once, I just don't know how to do it again). Many Mindy

• Answer:

  Your task looks like a do-able process, but I would solve it with a combination of a pivot table and the vlookup function. Are you familiar with either or both of these? First, I would remove the subtotals from the data sheet. Second, I would create a pivot table from the now revised data sheet. Pivot tables have the ability to group dates by day, week, month, quarter, year, etc. You could then take your summary sheet and with a slight modification or addition to the week column, use the vlookup function to return your actual data from the pivot table. You then only need to right-click and click refresh on your pivot table and it will update everything else automatically for you. I realize this is not the full detail, but I can help you implement if you want to reply and tell me whether or not you are familiar with pivot tables and vlookup. (If you aren't yet, these are two of the best features ever in excel!) -Craig