Excel - Insert Worksheet Name into Cell
Solved/Closed
bibidel
-
hugo -
hugo -
Hello,
Do you have the trick to insert the name of a worksheet tab in an Excel cell?
Thank you allConfiguration: Windows XP
Internet Explorer 6.0
Do you have the trick to insert the name of a worksheet tab in an Excel cell?
Thank you allConfiguration: Windows XP
Internet Explorer 6.0
34 réponses
- 1
- 2
Suivant
hello
Without using a macro, it is entirely possible with this formula:
ps: the display is incorrect because instead of &.q.u.o.t.; it should be "
--
always zen
Without using a macro, it is entirely possible with this formula:
for the sheet name =MID(CELL("filename");FIND("]";CELL("filename"))+1;20) or of course you can replace the length 20 by calculating the correct length but is it worth the effort? =MID(CELL("filename");FIND("]";CELL("filename"))+1;LEN(CELL("filename"))-FIND("]";CELL("filename"))) for the workbook name =MID(CELL("filename");FIND("[";CELL("filename"))+1;FIND("]";CELL("filename"))-FIND("[";CELL("filename"))-1) and the full path =CELL("filename") ps: the display is incorrect because instead of &.q.u.o.t.; it should be "
--
always zen
Intuitively, I would tell you to put this formula in each tab on a hidden row, then in the tab where you want to see everything, you create a table containing references to the contents of your hidden cells in each tab...
But I'm anything but an Excel expert, more of a tinkerer, so there’s probably a better way! :)
Good luck,
Galileo
But I'm anything but an Excel expert, more of a tinkerer, so there’s probably a better way! :)
Good luck,
Galileo
Hello,
Is there an issue with the formula for the tab name?
I used it for a file with several tabs and it seemed to work well except that I realized it actually takes the last opened tab. So if you go back to the tab where the formula is, it takes into account the previous tab.
A solution? Settings?
Is there an issue with the formula for the tab name?
I used it for a file with several tabs and it seemed to work well except that I realized it actually takes the last opened tab. So if you go back to the tab where the formula is, it takes into account the previous tab.
A solution? Settings?
Hello Seb,
Here is the formula that works in a cell, it seems to correspond to your "happiness":
=MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;20)
Best,
Bibidel
Here is the formula that works in a cell, it seems to correspond to your "happiness":
=MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;20)
Best,
Bibidel
Hello,
For the formula to work in a workbook with multiple tabs, you need to reference a cell from the sheet
=MID(CELL("filename",B1),FIND("]",CELL("filename",B1))+1,LEN(CELL("filename",B1))-FIND("]",CELL("filename",B1)))
For the formula to work in a workbook with multiple tabs, you need to reference a cell from the sheet
=MID(CELL("filename",B1),FIND("]",CELL("filename",B1))+1,LEN(CELL("filename",B1))-FIND("]",CELL("filename",B1)))
You did exactly what you had to do, adding the optional cell reference, and that way you point to a cell in your sheet (instead of the active sheet...).
You can leave A1 in each formula (no link between the 2 of A2 and the 2 of sheet2) and therefore no increment needed, it's the same formula on all sheets.
And also add it to the first CELL(...;A1) to make it look cleaner.
eric
You can leave A1 in each formula (no link between the 2 of A2 and the 2 of sheet2) and therefore no increment needed, it's the same formula on all sheets.
And also add it to the first CELL(...;A1) to make it look cleaner.
eric
Hello,
Overall in agreement with dandan, and to have the active tab instead of sheet 1, you would need:
Cells(1, 1) = ActiveSheet.Name
and to get all the names, you can do a loop:
Sub name()
For Each sh In Sheets
i = i + 1
Cells(i, 1) = sh.Name
Next sh
End Sub
Otherwise, it is possible with a formula as long as you are in another sheet:
for example, in sheet2 enter:
=CELL("address";Sheet1!A2) => [Workbook1]Sheet1!$A$2
You just need to process the string to extract its name.
eric
Overall in agreement with dandan, and to have the active tab instead of sheet 1, you would need:
Cells(1, 1) = ActiveSheet.Name
and to get all the names, you can do a loop:
Sub name()
For Each sh In Sheets
i = i + 1
Cells(i, 1) = sh.Name
Next sh
End Sub
Otherwise, it is possible with a formula as long as you are in another sheet:
for example, in sheet2 enter:
=CELL("address";Sheet1!A2) => [Workbook1]Sheet1!$A$2
You just need to process the string to extract its name.
eric
Hello
this works well, it's quite possible with this formula that gives
the name of the tab
=MID(CELL("filename");FIND("]";CELL("filename"))+1;20)
or of course it's possible to replace the length 20 by calculating the correct length, but is the game worth the candle?
as follows;
=MID(CELL("filename");FIND("]";CELL("filename"))+1;LEN(CELL("filename"))-FIND("]";CELL("filename")))
and the full path
=CELL("filename
this works well, it's quite possible with this formula that gives
the name of the tab
=MID(CELL("filename");FIND("]";CELL("filename"))+1;20)
or of course it's possible to replace the length 20 by calculating the correct length, but is the game worth the candle?
as follows;
=MID(CELL("filename");FIND("]";CELL("filename"))+1;LEN(CELL("filename"))-FIND("]";CELL("filename")))
and the full path
=CELL("filename
Good evening,
Response to post 54.
To list all the sheet names, the workbook must first be saved, then:
" insert / name / define... "
'name in the workbook :' name_sheets
'Refers to :' =RAND()*0&TRANSPOSE(READ.BOOK(1))
Confirm
On line 1 of a sheet enter:
=MID(INDEX(name_sheets;ROW());SEARCH("]";INDEX(name_sheets;ROW()))+1;30)
to be copied down.
The #REF! from non-existent sheets can be eliminated with an additional test if needed (but this makes the formula heavier)
eric
Response to post 54.
To list all the sheet names, the workbook must first be saved, then:
" insert / name / define... "
'name in the workbook :' name_sheets
'Refers to :' =RAND()*0&TRANSPOSE(READ.BOOK(1))
Confirm
On line 1 of a sheet enter:
=MID(INDEX(name_sheets;ROW());SEARCH("]";INDEX(name_sheets;ROW()))+1;30)
to be copied down.
The #REF! from non-existent sheets can be eliminated with an additional test if needed (but this makes the formula heavier)
eric
Hello
They work well but there is a display error on the site: check the PS
You need to replace "&.q.u.o.t.;" (the intercalated dots for display) with the quote simply.
I try again
PS: still not good... it goes over two lines and the two need to be grouped in the same cell
--
always zen
They work well but there is a display error on the site: check the PS
You need to replace "&.q.u.o.t.;" (the intercalated dots for display) with the quote simply.
I try again
=MID(CELL("filename");FIND("[";CELL("filename"))+1;FIND("]";CELL("filename"))-FIND("[";CELL("filename"))-1) PS: still not good... it goes over two lines and the two need to be grouped in the same cell
--
always zen
Hello
Here is a small custom VBA function
Function SheetName() As String
Application.Volatile
SheetName = Application.Caller.Parent.Name
End Function
Here is a small custom VBA function
Function SheetName() As String
Application.Volatile
SheetName = Application.Caller.Parent.Name
End Function
Thank you again to Eric and gbinforme
Here’s how I solved my issue temporarily, but I'm looking for a better solution:
It’s not great, but I don’t know how to increment (without macros) the sheet number in the workbook, so for each cell in each sheet, I modify the formula by changing (“filename”,A1) for sheet 1 (“filename”,A2) for the second, and so on...
The key is not to move the sheets.....
=MID(CELL("filename");FIND("]";CELL("filename";A1))+1;20) for sheet 1
=MID(CELL("filename");FIND("]";CELL("filename";A2))+1;20) for sheet 2
and so on...
See you soon
Bibidel
Here’s how I solved my issue temporarily, but I'm looking for a better solution:
It’s not great, but I don’t know how to increment (without macros) the sheet number in the workbook, so for each cell in each sheet, I modify the formula by changing (“filename”,A1) for sheet 1 (“filename”,A2) for the second, and so on...
The key is not to move the sheets.....
=MID(CELL("filename");FIND("]";CELL("filename";A1))+1;20) for sheet 1
=MID(CELL("filename");FIND("]";CELL("filename";A2))+1;20) for sheet 2
and so on...
See you soon
Bibidel
The formula to enter in the destination cell is as follows.
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,20)
20 corresponds to the maximum number of characters for the sheet name, and you can give it any value you want.
Bididel
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,20)
20 corresponds to the maximum number of characters for the sheet name, and you can give it any value you want.
Bididel
Good evening,
I can play too?
So weirdly this formula is exactly the same as yours bibidel, but if I copy/paste yours I also get an error (??)
I keep looking and I can't see the difference. Well, a tiny one in length as if a character was slightly wider...
So I pasted it as code. If there are still problems, I advise SebastienC44 to re-enter in Excel what is found between the ""..." including the "" even for ""[". A priori that's where it's bugging.
Weird, weird...
eric
I can play too?
=MID(CELL("filename";A1);FIND("]";CELL("filename";A1))+1;20) So weirdly this formula is exactly the same as yours bibidel, but if I copy/paste yours I also get an error (??)
I keep looking and I can't see the difference. Well, a tiny one in length as if a character was slightly wider...
So I pasted it as code. If there are still problems, I advise SebastienC44 to re-enter in Excel what is found between the ""..." including the "" even for ""[". A priori that's where it's bugging.
Weird, weird...
eric
Hello
while checking the forum resolutions (to try to improve myself) I attempted to answer
bibbel's question; it works for me with this:
Private Sub Worksheet_Activate()
[a1] = ActiveSheet.Name
End Sub
can you tell me if this solution answers bibbel's post no. 1 and if it is incongruous compared to the provided solutions
thanks and see you later
while checking the forum resolutions (to try to improve myself) I attempted to answer
bibbel's question; it works for me with this:
Private Sub Worksheet_Activate()
[a1] = ActiveSheet.Name
End Sub
can you tell me if this solution answers bibbel's post no. 1 and if it is incongruous compared to the provided solutions
thanks and see you later
Hello,
I went through your discussion thinking I had found my happiness:
I'm looking for a function that will allow me to display the name of the tab of this sheet in any cell of my sheet.
I copied the given formula but it doesn't work. Did I understand the purpose of your formula correctly? If so, why doesn't it work? If not, is it possible to do what I want?
Thank you very much,
Seb
I went through your discussion thinking I had found my happiness:
I'm looking for a function that will allow me to display the name of the tab of this sheet in any cell of my sheet.
I copied the given formula but it doesn't work. Did I understand the purpose of your formula correctly? If so, why doesn't it work? If not, is it possible to do what I want?
Thank you very much,
Seb
Good evening Seb,
The formula to type in the destination cell is as follows and it should match your "happiness"
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,20)
20 corresponds to the maximum number of characters for the sheet name, you can give it any value you want.
Best regards,
Bibidel
The formula to type in the destination cell is as follows and it should match your "happiness"
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,20)
20 corresponds to the maximum number of characters for the sheet name, you can give it any value you want.
Best regards,
Bibidel
I confirm the remark made by bibidel that it doesn't work when pasting this kind of formula in multiple tabs of the same workbook.
Specifically, I want to see, for example, the name of the tab in cell A1 of each tab.
If I paste the formula =RIGHT(...) or the formula =MID(...) in each of the tabs, the same value will display everywhere, namely the name of the tab from which I activated the F9 recalculation key.
This formula does not therefore meet the need in the configuration I described.
Specifically, I want to see, for example, the name of the tab in cell A1 of each tab.
If I paste the formula =RIGHT(...) or the formula =MID(...) in each of the tabs, the same value will display everywhere, namely the name of the tab from which I activated the F9 recalculation key.
This formula does not therefore meet the need in the configuration I described.
The formula is as follows to type in the destination cell
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,20)
20 corresponds to the max number of characters for the sheet name, and we can give it any value we want.
Bibidel
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,20)
20 corresponds to the max number of characters for the sheet name, and we can give it any value we want.
Bibidel
- 1
- 2
Suivant
Very useful