How do you reference another worksheet without using its name

[dallen917 via OfficeKB.com]

The standard way to reference a cell on another worksheet within the same
file is =Sheet2!A1. How can I create a reference that basically says A1 in
the next worksheet irregardless of its name (i.e., current worksheet + 1).

 

[Harlan Grove]

The standard way to reference a cell on another worksheet within the same
file is =Sheet2!A1.  How can I create a reference that basically says A1 in
the next worksheet irregardless of its name  (i.e., current worksheet + 1).

No way to do this without either using names defined using XLM
functions or using user-defined functions written in VBA. Both trigger
warnings when the workbook opens when macro security is set to Medium
and would be disabled when macro security is set to High or Very High
unless you provide certificates for the VBA project.

Actually, if your worksheet names NEVER change and you use workBOOK
protection to prevent users reordering worksheets, you could put an
ordered list of worksheet names into a range named WSLST, define a
name like WS referring to the formula

=MID(CELL("Filename",INDIRECT("RC",0)),
FIND("]",CELL("Filename",INDIRECT("RC",0)))+1,32)

then you could use a formula like

=INDIRECT("'"&INDEX(WSLST,MATCH(WS,WSLST,0)+1)&"'!"&
CELL("Address",INDIRECT("RC",0)))

to refer to the cell at the same address in the next worksheet.