Formulas That Reference ADJACENT Sheet

[mlman]

I am creating a form in Excel 2003 where I need the formulas in the sheet to
reference the adjacent sheet.

i.e. I have 3 sheets called 'May 19', 'May 18' & 'May 17'. Formulas in 'May
19' reference cells in 'May 18', formulas in 'May 18' references cells in
'May 17' and so on.

The problem occurs when I copy a sheet. If I copy sheet 'May 19' and rename
it 'May 20' I want 'May 20' formulas to reference sheet 'May 19' but they
instead reference sheet 'May 18' because I copied the 'May 19' sheet.

Is there anyway to reference the adjacent sheet automatically without having
to change the formulas?

 

[Chip Pearson]

Excel itself has no mechanism for relative sheet referencing. All
direct references must be explicit. However, with some simple VBA
code, you can get the sheet that is either before or after the sheet
containing the formula and use that in INDIRECT to get the value of a
cell on the next or previous sheet. Open the VBA editor (ALT F11) and
then view the Project window on the left side of the screen (CTRL R to
view if it is not open). Select your workbook's project in the Project
window and then go to the Insert menu and choose "Module". In that
code module, paste the following:

Function NextSheetName() As String
Application.Volatile True
On Error Resume Next
NextSheetName = "'" & Application.Caller.Worksheet.Next.Name & "'"
If Err.Number <> 0 Then
NextSheetName = vbNullString
End If
End Function

Function PreviousSheetName() As String
Application.Volatile True
On Error Resume Next
PreviousSheetName = "'" &
Application.Caller.Worksheet.Previous.Name & "'"
If Err.Number <> 0 Then
PreviousSheetName = vbNullString
End If
End Function

Close the VBA editor and return to Excel. Now, you can use
NextSheetName and PreviousSheetName in formulas with INDIRECT. For
example, to get the value of A1 on the sheet following the current
sheet, use

=INDIRECT(NextSheetName()&"!A1")

Similarly, for the previous sheet,

=INDIRECT(PreviousSheetName()&"!A1")

NextSheetName and PreviousSheetName always return the sheet after or
before the sheet that contains the formula that called them. If you
move sheets around, the formulas will still return the newly next and
previous sheet names.

You'll get an error if you call NextSheetName from the last sheet in
the workbook or if you call PreviousSheetName from the first worksheet
in the workbook.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com

 

[Steve Dunn]

Change A1 to the cell reference you need.

=INDIRECT("'"&MID(CELL("filename",A1),
FIND("]",CELL("filename",A1))+1,
FIND(" ",CELL("filename",A1))-FIND("]",CELL("filename",A1)))&
TRIM(RIGHT(CELL("filename",A2),2))-1&"'!A1")

HTH
Steve D.

 

[Steve Dunn]

Sorry, should clarify: change only the very last A1 to the cell reference
you require on the earlier sheet.

The A1s within CELL() are only a way for CELL() to reference the sheet
containing the formula, and can refer to any cell within that sheet.

Change A1 to the cell reference you need.

=INDIRECT("'"&MID(CELL("filename",A1),
FIND("]",CELL("filename",A1))+1,
FIND(" ",CELL("filename",A1))-FIND("]",CELL("filename",A1)))&
TRIM(RIGHT(CELL("filename",A2),2))-1&"'!A1")

HTH
Steve D.

I am creating a form in Excel 2003 where I need the formulas in the sheet
to
reference the adjacent sheet.

i.e. I have 3 sheets called 'May 19', 'May 18' & 'May 17'. Formulas in
'May
19' reference cells in 'May 18', formulas in 'May 18' references cells in
'May 17' and so on.

The problem occurs when I copy a sheet. If I copy sheet 'May 19' and
rename
it 'May 20' I want 'May 20' formulas to reference sheet 'May 19' but they
instead reference sheet 'May 18' because I copied the 'May 19' sheet.

Is there anyway to reference the adjacent sheet automatically without
having
to change the formulas?

 

[mlman]

Thanks for your help! Chip Pearson's function works great.