Display Worksheet title in cell

  • Thread starter Thread starter treen
  • Start date Start date
T

treen

Is there any way to display a worksheet name in the cell of another
worksheet. Example: Worksheet names 1, 2, 3, Summary. I want the Names 1,2
and 3 to display in cells on the Summary worksheet. These sheets are all in
the same workbook.

I have seen answer for getting the worksheet name to display in a cell on
the same worksheet but not different worksheet names.
 
Try this...

Create this named formula
Goto the menu Insert>Name>Define
Name: SheetNames
Refers to:

=GET.WORKBOOK(1)&T(NOW())

OK

Assume you want the names listed on your Summary sheet starting in cell A1.

Enter this formula in Summary A1:

=INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(A$1:A1))

Copy down until you get a #REF! error. Delete all the cells that return the
#REF! error.
 
Hi,

Found following reply to another question, download this addin and you will
have the required functions you are looking for.

=FILENAME
=SHEETNAME


Thanks to NBVC.

Wkr,

JP

Download the free Morefunc.xll addin from here:
http://download.cnet.com/Morefunc/3000-2077_4-10423159.html

and use this formula:

=SUBSTITUTE(TRIM(MCONCAT(IF(ISTEXT(A1:G1),A1:G1,"")," "))," ",",")

which must be confirmed with CTRL+SHIFT+ENTER not just ENTER... after
you have adjusted the ranges to suit.


--
NBVC

Where there is a will there are many ways.

'The Code Cage' (http://www.thecodecage.com)
 
Hi,
In the cell where you want the sheet name enter the formula as follow

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("filename",A1),1)-FIND("[",CELL("filename",A1),1)-1)
 
Very nice-- works fine and is very helpful...
How about a list of only VISIBLE worksheets?

-KIM W

T. Valko said:
Try this...

Create this named formula
Goto the menu Insert>Name>Define
Name: SheetNames
Refers to:

=GET.WORKBOOK(1)&T(NOW())

OK

Assume you want the names listed on your Summary sheet starting in cell A1.

Enter this formula in Summary A1:

=INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),ROWS(A$1:A1))

Copy down until you get a #REF! error. Delete all the cells that return the
#REF! error.

--
Biff
Microsoft Excel MVP


treen said:
Is there any way to display a worksheet name in the cell of another
worksheet. Example: Worksheet names 1, 2, 3, Summary. I want the Names 1,2
and 3 to display in cells on the Summary worksheet. These sheets are all
in
the same workbook.

I have seen answer for getting the worksheet name to display in a cell on
the same worksheet but not different worksheet names.
Click to expand...
Click to expand...
 
Back
Top