Results 1 to 2 of 2
Thread: Chain Excel Sheets (any)
2008-04-28, 06:01 #1
- Join Date
- Jan 2001
- Kendrick, Idaho, USA
- Thanked 0 Times in 0 Posts
Chain Excel Sheets (any)
I have a file with each sheet representing a week. B6 of the Week1 is a beginning value. C6 thru G6 are values for Monday thru Friday and H6 is the total. B6 of the Week 2 (the next sheet) references H6 on the Week 1 sheet. Week 3!B6 references Week2!H6. Each sheet's sequence pulls the total from the previous sheet. All sheets are arranged exactly alike, with the same 7 active locations: the total from the previous week, 5 values, and a new total which will be used in the next sheet.
I have to set up a full year with this format. Is there a way to streamline chaining one sheet to the next without manually setting up the reference in each sheet? If I could set up one and group the remaining sheets and paste into them with a relative formula, my life would be easier.
2008-04-28, 09:11 #2
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
Re: Chain Excel Sheets (any)
I'd prefer to enter all data in a single sheet, one row per day. You can then use a pivot table to summarize the data by week.
If you prefer the current setup, you could use the INDIRECT function to refer to the previous week's worksheet provided that you name the sheets consistently. For example, if the worksheets are named Week1, Week2, ..., you could do the following:
- Enter the week number in a cell, say A1.
- Group the sheets for weeks 2 through 52.
- Enter the following formula in Week2!B6: