Software

Ask something
Discussion Locked

show another sheet name in a cell

0 helpful
3 replies

by tej_dyal

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I would like to know how to show (by link) another sheet name in a cell in Microsoft Excel. ie if I have first sheet called SHEET1 and 2ndd called SHEET2. I want say Sheet1!A1 cell to show SHEET2 without by link without manually typing it. Thank you.

3 replies

by shanghai sam

It would help if I new the purpose of showing the Sheet name.
In general, the formula to display the sheet name is:

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
If you used that formula in a cell of each sheet, you could refer to it.

(BTW the Workbook must have been saved for this to work correctly)
To extract the Sheet name from a formula, write a short Function

Function CellFormula(c) As String
If c.HasFormula Then
CellFormula = c.Formula
Else
CellFormula = ""
End If
End Function

You can then use the following formula to display the Sheet name referenced by a formula

=MID(LEFT(Cellformula(A2),FIND(?!?,Cellformula(A2))-1),2,255)
or something like

=IF(ISERROR(MID(LEFT(cellformula(L2),FIND("!",cellformula(L2))-1),2,255)),"No sheet is referenced",MID(LEFT(cellformula(L2),FIND("!",cellformula(L2))-1),2,255))

by tej_dyal

Excellent solution, Its just what I required and very quick response. My reason for its use is to index sheet names and to also use it as top row title.

This discussion is closed to new replies.