In Excel, type `Alt`+`F11` to open the Visual Basic window.
On the left, right-click on the name of your spreadsheet and `Insert`

→ `Module`

.
A `Module1 - Code`

sub-window will open on the right. Insert the following in that window:

```
Function SHEET_NUM()
SHEET_NUM = Application.Caller.Parent.Index
End Function
```

Now go back to the spreadsheet. Just for fun, to see how this works, type `=SHEET_NUM()`

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))
```

into cell `H4`

on every sheet. The `SHEET_NUM()+1`

subexpression will evaluate to `2`

on `Sheet1`

, `3`

on `Sheet2`

, etc… –– in other words, the row number that you want in your reference.
`&`

is the concatenate operator; it will combine the constant string `"[sub.xlsx]Sheet1!$B$"`

with the computed row number, resulting in the reference that you want.
The `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 (`sub.xlsx`

),
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.)