Sheet name in a cell

[Joann L.]

I have a total sheet and want to add up the previous
sheets by name. For example if the sheets are named
Assets, Income, Liabilites. On the total sheet in Cell
A1 I want the word Assets from the sheet name and then I
will place the value from the sheet in B1. Therefore if
I rename a sheet I know where the total came from.
Thanks

 

[Chip Pearson]

Joann,

The following formula will return the sheet name.

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


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Andy]

The CELL function can be used to give the address of a cell. This returns a
the workbook name, sheetname and the address of the cell on the sheet eg
[Book1.xls]sheet1!A1.

By looking for the ] and the ! we can then select the part of the address as
follows:

=MID(CELL("address",Sheet2!C16),FIND("]",CELL("address",Sheet2!C16))+1,FIND(
"!",CELL("address",Sheet2!C16))-FIND("]",CELL("address",Sheet2!C16))-1)

Sheet2 can be replaced by each sheet in your workbook.
C16 can be replaced by any cell on the sheet.

hth

AndyO

 

[joannl]

Okay that works for the sheet you are on but what if I
want to have a summary sheet and each sheet in the
workbook referenced on that summary sheet. For example
On the summary Sheet
Sheetname Accounts 100
Sheet name Receivables 100
(sheet name is just for example the sheets are Accounts
and Receivables).
Thanks

-----Original Message-----
Joann,

The following formula will return the sheet name.

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


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

I have a total sheet and want to add up the previous
sheets by name. For example if the sheets are named
Assets, Income, Liabilites. On the total sheet in Cell
A1 I want the word Assets from the sheet name and then I
will place the value from the sheet in B1. Therefore if
I rename a sheet I know where the total came from.
Thanks

.