We are trying to find out if there is a way to reference a tab name and have
it print in a cell.

i.e. tab a is named Testing Sheet, we want the name of the tab to also be
shown in Cell A3. Is there a way to reference this without retyping? We have
multiple worksheets and are trying to do a copy/paste formula to
reference/enter worksheet/tab names.

Thank you for any help.

=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)
--
Gary''s Student
gsnu200707


"DLO" wrote:

We are trying to find out if there is a way to reference a tab name and have
it print in a cell.

i.e. tab a is named Testing Sheet, we want the name of the tab to also be
shown in Cell A3. Is there a way to reference this without retyping? We have
multiple worksheets and are trying to do a copy/paste formula to
reference/enter worksheet/tab names.

Thank you for any help.

This should work:

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

Note that the workbook must first be saved for this to return the correct
result.

HTH,
Elkar


"DLO" wrote:

We are trying to find out if there is a way to reference a tab name and have
it print in a cell.

i.e. tab a is named Testing Sheet, we want the name of the tab to also be
shown in Cell A3. Is there a way to reference this without retyping? We have
multiple worksheets and are trying to do a copy/paste formula to
reference/enter worksheet/tab names.

Thank you for any help.

Try this:

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

The file must already exist. (must have a name and have been saved)

Biff

"DLO" wrote in message
...
We are trying to find out if there is a way to reference a tab name and
have
it print in a cell.

i.e. tab a is named Testing Sheet, we want the name of the tab to also be
shown in Cell A3. Is there a way to reference this without retyping? We
have
multiple worksheets and are trying to do a copy/paste formula to
reference/enter worksheet/tab names.

Thank you for any help.

Can one of you explain to me how it works, it makes it much easier for me to
understand how to fix it if something doesn't work, also what is the last
number and why did people mention two different ones?

Thanks!

"DLO" wrote:

We are trying to find out if there is a way to reference a tab name and have
it print in a cell.

i.e. tab a is named Testing Sheet, we want the name of the tab to also be
shown in Cell A3. Is there a way to reference this without retyping? We have
multiple worksheets and are trying to do a copy/paste formula to
reference/enter worksheet/tab names.

Thank you for any help.

This formula basically looks at the complete path (filename and sheetname) of
the referenced cell. Since the cell reference doesn't really matter, we just
used cell A1.

It might be easier to understand if you break the formula down into seperate
parts. Try entering just this:

=CELL("filename",A1)

The result should be something like: C:\Excel Files\[myfile.xls]sheet1

We then take this result and manipulate it using the MID function (this
extracts info from a text string, starting somewhere in the middle). To
determine where the starting point for the MID function will be, we use the
FIND function to locate the "]" symbol, which immediately precedes the
sheetname. The final number (255 or 999 or whatever) is simply the number of
characters we want to return from the established starting point. The number
really doesn't matter, so long as it is large enough to include your entire
sheet name.

Hopefully that makes sense.
Elkar


"DLO" wrote:

Can one of you explain to me how it works, it makes it much easier for me to
understand how to fix it if something doesn't work, also what is the last
number and why did people mention two different ones?

Thanks!

"DLO" wrote:

We are trying to find out if there is a way to reference a tab name and have
it print in a cell.

i.e. tab a is named Testing Sheet, we want the name of the tab to also be
shown in Cell A3. Is there a way to reference this without retyping? We have
multiple worksheets and are trying to do a copy/paste formula to
reference/enter worksheet/tab names.

Thank you for any help.