To get, in a formula, the current sheet name, you can use the
following expression:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

In a separate area in your workbook you can have a list of all
worksheets, in the order in which they appear in Excel (and you want
to use them). Say this is in sheet List and occupies cells A1:A10. You
can define a name (InsertNameDefine...) for this range, say shList.

Now, instead of pointing to the data of a previous sheet with a
formula such as:

='Sheet 1'!A1

You can use INDIRECT in the following manner:

=INDIRECT("'"&INDEX(shList,MATCH(MID(CELL("filenam e",A1),FIND("]",CELL("filename",A1))
+1,255),shList,0)-1)&"'!A1")

You can then copy this formula in any other sheet and it will refer to
the previous sheet.

HTH
Kostis Vezerides

On Jul 5, 6:20 pm, 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.

Susan