In Excel, type Alt+F11 to open the Visual Basic window.
On the left, right-click on the name of your spreadsheet and
Module1 - Code sub-window will open on the right. Insert the following in that window:
SHEET_NUM = Application.Caller.Parent.Index
Now go back to the spreadsheet. Just for fun, to see how this works, type
into any cell. It should display the number of the sheet that it’s on. So, enter
=INDIRECT("[sub.xlsx]Sheet1!$B$" & (SHEET_NUM()+1))
H4 on every sheet. The
SHEET_NUM()+1 subexpression will evaluate to
Sheet2, etc… –– in other words, the row number that you want in your reference.
& is the concatenate operator; it will combine the constant string
with the computed row number, resulting in the reference that you want.
INDIRECT function takes a string that looks like a reference and de-references it; i.e., it fetches the value that is referenced.
A few warnings:
- If you’re dealing with one workbook (
stu.xlsx) referencing another (
you will need to have the second book open whenever you work with the first one
–– although Excel can reference data in a closed book, the
INDIRECT function cannot.
If this is a problem, you can do a Copy and Paste Values maneuver.
- You will need to save your workbook as an
.xlsm file and enable macros every time you open it.
(Or do the aforementioned Copy and Paste Values maneuver.)
- AFAICT, worksheets are assigned a number when they are created, and it’s hard to change.
In particular, if you move your sheets around, they will retain their original numbers,
which will be confusing.
(But on the other hand, you can rename them without worrying about changing their numbers.)