sheet2 references sheet1 problem
Posted by Richie on November 27, 2001 6:22 AM
Sheet1 tracks daily sales in column F with weekly totals every 7 days.
Sheet2 is emailed to my corporation with 1 week's worth of sales and a total.
Sheet2 references sheet1 okay, but I have to change each cell reference manually for all 7 days every week to keep the sales current on sheet2.
Is there a way that the sheet2 cell references (to sheet1) can auto change every week?
I afraid this is as clear as mud!
Thanks for any help!
Richie
Posted by Tom Dickinson on December 01, 2001 11:10 AM
One method would be to use offset. That way you can change the number in one cell rather than each formula.
Example:
Monday summary formula is =OFFSET(Sheet1!F1,A1*7,0)
Tuesday summary formula is =OFFSET(Sheet1!F2,A1*7,0)
The Sheet1!Fx references the first weeks numbers.
The A1*7 is the offset for the week you want. Put the week number in cell A1 (the first week is week 0)
If your weekly recording is not uniform in the number of rows used, you could have each formula have its own offset cell.
Monday summary formula is =OFFSET(Sheet1!F1,A1-1,0)
Tuesday summary formula is =OFFSET(Sheet1!F1,A2-1,0)
In this case, just put in the row number you want displayed into cells A1 to A7. I put in a "-1" so that you would not have to keep remembering that it is offset style numbering.
