Susan,

If you are confused about the syntax of an Excel function, such as CELL,
then the best bet is to type its name into Excel's help. It'll tell you
about the syntax, give examples, & usually tell you about related functions
through its "See also" link.
--
David Biddulph

"Susan" wrote in message
...
Should "filename" be the name of my workbook? And is A1 referring to the
shList or to the cell that I'm entering this formula into? When I enter
the
formula as given, it returns #VALUE!

"vezerid" wrote:

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