"Susan" wrote...
How can I reference a worksheet tab in a formula, so that it always refers
to the previous tab in a workbook, such as a formula in Tab 3 needs info
from the same cell in Tab 2 in order to keep track of balances. When I
copy and paste the spreadsheets into a new worksheet it keeps the reference
that was in the original worksheet.

Yet another approach, this time involving XLM functions (so dangerous in
Excel 2000 and prior). Define the name _WSLST referring to the formula

=SUBSTITUTE(GET.WORKBOOK(1),"["&GET.DOCUMENT(88)&"]","")

Then select a blank row in some worksheet, name it WSLST and enter the array
formula

=_WSLST

in it. Also define the name _WBWS referring to the formula

=CELL("Filename",!$1:$65536)

and the name WSNAME referring to the formula

=MID(_WBWS,FIND("]",_WBWS)+1,32)

With this setup you can retrieve the name of the previous worksheet with

=INDEX(WSLST,MATCH(WSNAME,WSLST,0)-1)

[I'm not sure whether using WSLST in place of _WSLST fixes the problem in
Excel 2000 and prior in which copying a range containing formulas that
refers to names that directly call XLM functions and pasting into other
worksheets crashes Excel. Maybe referring to a range instead prevents this.
Anyone else what to check?]