[RESOLVED] Creating blocks with relative formulas

[westconn1]

i am trying to create a summary sheet for payroll and want to use formulas to get data from other sheets

each sheet has column A (a5:a68) containing weekly dates, with empty row between months and quarterly totals
i want to transcribe 3 columns (J,L and M) for each quarter and annual total, to summary sheet, sheet names are employee names + date



i can use a formula, =VLOOKUP("Total1",INDIRECT(CONCATENATE("'",C1," ",A1,"'!a5:n70")),10,FALSE) (thnx tntinmn), in C3 to reference the values from other sheets, but i would like to make all references relative to employees, so for rows 3 - 8 columns d,e, and f will look at C1, g,h and i will look at G1 etc, i can merge the cells in row 1 if that would help, also the column index for the vlook up can be somehow be matched to each required columns (10, 11 and 13)

the reason for doing this is so i can copy and paste the blocks as required, without have to edit all the formulas each time

i am attaching a sample workbook, i have, now, modified the total for each quarter to Total1 etc, to make the vlookup easier
sample.zip

new sheets are created automatically each period, or when required, so i would like to make as few changes as possible to the main sheets, but now is the best time to do it, before new sheets next month

[TnTinMN]

Pete,

Take a look at this "Pete's sample.zip" and see its works for you.

The new formula is shown on the "Summary - derivation" tab.

[westconn1]

thank you so much

testing so far is totally positive
while i pretty much understand all the functions, i would not have been able to put it together like that

i changed the worksheets so the values in column A match the lookups in column A

[TnTinMN]

thank you so much

You are welcome.

i changed the worksheets so the values in column A match the lookups in column A

I forgot that I was going to suggest that you use Named Range Array to store the ranges for the different quarters, then that name could be incorporated into the formula. But whatever works for you.