For starters, let me thank Dick for opening his Blog to us. It prevents me having to go through all the trouble of starting one myself :-).
When referring to worksheets in a formula, it may sometimes be useful to have a method to refer to the sheet to the immediate left or right of the worksheet that contains the formula, regardless whether the worksheets are moved. Unfortunately Excel provides no direct method to do this.
There are a couple of ways one can do this. The obvious one is to use a VBA function that gets the previous or next worksheet’s name. But I thought it might be instructive to show a non-VBA solution that involves the use of (ancient) XL4 Macro functions in defined names.
Now let us get this up and running.
Start Excel and open the workbook in which you want to start using this.
Now define these names (Insert, Name, Define):
Gets an array of all sheets in the workbook
Gets the name of the sheet the name is used in.
Gives the name of the worksheet to the immediate left of the sheet where this name is used.
Gives the name of the worksheet to the immediate right of the sheet where this name is used.
The 0*Now() is added to ensure the names are “volatile” and get recalculated on every calculation of Excel.
To refer to cell B1 on the previous worsheet, use this formula:
Likewise for the next worksheet:
To get the name of the previous sheet in a cell, use:
One serious warning is needed though.
Do not copy any cell that uses any of these defined names to another worksheet. Excel will crash!
Jan Karel Pieterse