Excel - Insert Worksheet Name into Cell

hello

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

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

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)))

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

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

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

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

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

=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

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

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

Good evening,

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

Yes, surely a setting somewhere (Word?) that converts.

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

This formula does not work in Microsoft Office 2007,

This one does...

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

THANK YOU Ériiiiiiiiic! Life seems so simple afterwards!

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

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

Alright Eriiic! pssssssss I've been found out
Catch you later!

Hello and thank you for your numerous responses. I copied the formula into a cell on Sheet1, but it doesn't work (#VALUE!). I then read that I needed to copy everything between """", but still nothing?
Is there a trick I'm missing?

--

Seb