Autofill formulas for non-contiguous (but consistent) cells on another sheet. EX INCLUDED

[njmiller31]

Hey All,

I have a workbook that I use to track several aspects of my job. There are 2 sheets: A calendar-style data sheet where I enter my daily numbers, and a sheet that summarizes the data. See attached example:

EDIT: Link was to wrong file... See below for correct file:
autofill example edit.xlsx

Since the summary sheet references non-contiguous cells in the data sheet, I'm unable to use the autofill feature. And since I have 11 different summary categories for every day that I work, entering the formulas manually is proving to be a very time-consuming task.

Is there an easier way to reference non-contiguous, but predictable, cells from the data sheet?

Thanks in advance for your help.

[Pauleyb]

Something didn't transfer correctly. Your example file has links to another file (Pizza Tips and Wages.xlsm), so it is not working properly and hard to understand what you want to accomplish. For example, what are your 11 different summary categories? On your sample, I can see a category for each day...???

I think I can discern some of what you are trying to do. Try this in Summary!C3
=IF(INDEX(Data!$A$11:$AP$11,6*(COLUMN()-COLUMN($C$3))+1)="","",(SUM(OFFSET(Data!$B$11,0,6*(COLUMN()-COLUMN($C$3)),50))))
You should then be able to drag it across to I3.

Just a general pointer, however. It may be easier to have a very basic table that includes date, amount, tip, etc. and then create the tables/displays you want. A pivot table may be able to provide some of the summaries you desire. It's never very easy, however, to generate multiple views with the same data, but the initial layout can help.

Use the concepts in the formula above to adjust for your other needs. INDEX and OFFSET are very similar. Some prefer INDEX since it is less volatile, but I included both so you can see how they work.