Copying worksheet name onto a cell

[Umair Ali]

Hi,
I have 5 different worksheets in a workbook. Each
worksheet has its own different name. I was wondering if
there is a way to copy the name of each worksheet on one
of the cell of each worksheet. So say, if worksheet number
1 is called Total then one of the cell of that worksheet
referes to the name of the worsksheet and displays "Total".

I have tried using the function CELL("filename") but then
only gives me the path and whenever I change a cell, the
filename changes. Is there any other way of doing this.

Thanks,
Umair

 

[J.E. McGimpsey]

Look in Help for CELL(). It will tell you that the optional
reference parameter, if omitted, will cause CELL() to operate on the
last cell calculated (regardless of which sheet). So you need to
make sure that the last cell calculated for your sheet entries, is
the sheet that the entry is on:

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

It doesn't matter which cell, as long as it doesn't reference a
different sheet.

 

[John Tjia]

Do it in two steps:

1. Say you are in Sheet1.

In A1, write a CELL that references another sheet, say Sheet2. The
cell referenced doesn't matter, but let's stick to A1.

=CELL("address",Sheet2!A1)

This will return [Book1]Sheet2!$A$1


2. Then, write a formula that gets "Sheet2" out of A1. You can use a
MID function that gets the section between "]" and "!", which means
you have to find these characters using the FIND function. So to
break out the pieces:

In B1, write: =FIND("]",A1,1) which returns 7
In C1, write: =FIND("!",A1,1) which returns 14
In D1, write: =MID(A1,B1+1,C1-B1-1) which is working extracting the
string of characters in "A1", starting at character 8, for a length of
6 characters.

Repeat for all the other sheets on different rows. This will give you
in column D "live" names of the sheets in your workbook. This should
work with whatever filename and sheet names you are using.

Hope this is what you were looking for.