paste sheet name in a cell

[Support - Assetage]

Hi

How can I paste the name of "Sheet2" in a cell in "Sheet1" so that
the cell displays as text - "Sheet2"
and in case I change the name of "Sheet2" to "Sheet2A"
the text displayed in that cell also changes to "Sheet2A"

Thanks
Sanjay Jain
www.minisoftindia.com

 

[muddan madhu]

Try this one
put this formula in sheet2 A1
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)


In sheet 1 put A1 =Sheet2!A1

 

[muddan madhu]

Try this

In sheet 1 A1 put this
=MID(CELL("filename",sheet2!A1),FIND("]",CELL("filename",sheet2!A1))
+1,255)

 

[Shane Devenshire]

Hi Sanjay,

A few minor points regarding the previous suggestion:

1. Setting the last variable to 255 is not necessary since sheet names are
a max of 31 characters, so:

=MID(CELL("filename",Sheet2!A1),FIND("]",CELL("filename",Sheet2!A1))+1,31)

2. If you range name any cell in Sheet2, say "S", then you can simplify the
above formula to:

=MID(CELL("filename",S),FIND("]",CELL("filename",S))+1,31)

3. Since CELL("filename",S) is repeated you could define a name, say F, to
equal that and your formula would become:

=MID(F,FIND("]",F)+1,31)

You define a range name by choosing the cell and typing the name into the
Name Box. You define a formula name by choosing Insert, Name, Define and
entering the name in the Names in Workbook box and then the formula
=CELL("filename",S) in the Refers to box.

Cheers,
Shane Devenshire
Microsoft Excel MVP