How to copy a formula to multiple worksheets that references the previous worksheet

[robnsd]

I have a workbook with 31 identical worksheets, each representing a day of the month. I have several formulas I need to copy to those worksheets that refer to the previous day's worksheet (the one just before it.) If I copy the formula to all the worksheets, I have to go back to each one and change the worksheet name that the formula is referencing. Is there a way to make a formula reference the previous worksheet instead of an absolute worksheet?

[Tony Valko]

What version of Excel does this have to work in?

What are the REAL sheet names?

Are you simply referring to the same cell on the previous sheet? Like this:

On Sheet2: =Sheet1!A1
On Sheet3: =Sheet2!A1
On Sheet4: =Sheet3!A1
On Sheet5: =Sheet4!A1
etc
etc

[robnsd]

Yes, referring to the same cell on the previous sheet. I have Excel 2007. My sheet names are D (1), S (1), G (1), D (2), S (2), G (2), etc. So in reality three different sheets for each day (Day, Swing and Grave.)

[Tony Valko]

This is moderately complicated and may not be worth the trouble.

The sheets that this applies to must be physically located to the left of any other sheets there may be. For example, the sheet index will have to be like this:

\D (1)/\S (1)/\G (1)/\Master/\Notes/

This technique uses a macro function so you must enable macros and the file must be saved in the *.xlsm file format.

Create these defined names...

Name: SheetNames
Refers to: =GET.WORKBOOK(1)&T(NOW())

Name: SheetNum
Refers to: =GET.DOCUMENT(87)+NOW()*0

Apply the formula...

Let's assume you want to link to cell A1 from each previous sheet into cell A5 of the next sheet.

Group the sheets together that you want this to apply to.

Enter this formula in cell A5:

=INDIRECT("'"&INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),SheetNum-1)&"'!A1")

Make sure you ungroup the sheets after you're done!

Here's a small sample file created in Excel 2007 that demonstrates this.

[robnsd]

Tony,

Thank you very much. That worked great and I will use it. It sure looks complex. Is there any purpose for the master and notes tab or is that use a good convention to use. I'd be curious how this works. What is the significance of the number 87 in one of the names.

Robert

[Tony Valko]

Is there any purpose for the master and notes tab or is that use a good convention to use.

That is just to show how the sheets must be distributed. Those are totally random sheet names made up for the example.

What is the significance of the number 87 in one of the names.

87 is one of the argument numbers for the GET.WORKBOOK(...) function.

1 = return an array of the sheet names
87 = return an array of the sheet numbers

This info is not included in modern versions of Excel.