extracting a sheet reference name

[Richard]

Is there a function that can extract a sheet reference
name from an =Sheetname!cell so that another cell can
display the name as text?

 

[Bob Phillips]

Richard,

In a worksheet formula, this is the accepted way

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


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Norman Harker]

Hi Richard!



We often need to find the name of the sheet. We can use it in formulas
or we might print it as a footer or header or use it in some index or
table of contents.



The formula used is:



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



If you want to display just the sheet name of another sheet then the
reference A1 could be amended for that sheet:



=MID(CELL("filename",SecondSheet!A1),FIND("]",CELL("filename",SecondSh
eet!A1))+1,255)








--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Guest]

Thanks Bob

This is very usefull and I will make use of it in the
future.

However, my problem is...

=SheetName4!A10 formula is in SheetName2 and displays the
value (which is what I want).\

What I'm trying to do is display the SheetName4 in
SheetName2 from the formula that references SheetName4

SheetName2...
A ... E
10 =SheetName4!A10 ?formula
(value) Richard (value) SheetName4

SheetName4...
A
10 Richard

-----Original Message-----
Richard,

In a worksheet formula, this is the accepted way

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


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Is there a function that can extract a sheet reference
name from an =Sheetname!cell so that another cell can
display the name as text?

.

 

[Guest]

For furthur clarifiaction, columnA will contain SheetName
references from many different sheets and I want to
display to the user which sheet the value comes from.

Thanks again
Richard

 

[Peo Sjoblom]

It all depends on the system you are using to name the sheets, assuming that
SheetName4 is not the name. What are the names? Default Excel names would be
easy
using indirect and just increase the numbers and refer to it that way but if
there is no
obvious relations between the sheet names then you probably would need VBA
Here's an example with excel default names

Assume you have the sheet name in A1

=INDIRECT(A1&"!A10")

or

=INDIRECT("Sheet"&ROW(2:2)&"!A10")

which would be sheet2 and copied down it would return sheet3, sheet4 and so
on

--

Regards,

Peo Sjoblom

Thanks Bob

This is very usefull and I will make use of it in the
future.

However, my problem is...

=SheetName4!A10 formula is in SheetName2 and displays the
value (which is what I want).\

What I'm trying to do is display the SheetName4 in
SheetName2 from the formula that references SheetName4

SheetName2...
A ... E
10 =SheetName4!A10 ?formula
(value) Richard (value) SheetName4

SheetName4...
A
10 Richard

-----Original Message-----
Richard,

In a worksheet formula, this is the accepted way

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


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Is there a function that can extract a sheet reference
name from an =Sheetname!cell so that another cell can
display the name as text?

.

 

[Peo Sjoblom]

You would need a user defined function for that, see

http://www.mvps.org/dmcritchie/excel/formula.htm