Get Sheetname in Formula

[John Yazou]

Hi,
Just a quick one.. tried the archives, but can't seem to find the answer..
How do I get the current sheet name using formulas instead of vba??
Thanks!

 

[ukmikeb]

Hi

I have found that the formula posted by YasarShazad has a tendency in a multi-tab workbook to show an incorrect tab name.

PaddyD's formula doesn't display this characteristic and shows the correct tab name regardless of whether a cell has been changed on the sheet or not.

Thanks JonMo.

 

[YasarShahzad]

I tried it, and it doesn't.

Perhaps it only works on certain versions of Excel.

Here's what i did in Excel 2003:
0. Save the spreadsheet. Neither formula works if the spreadsheet is new and unsaved.
1. Create two tabs: "March 2013" and "April 2013."
2. Enter your formula into a cell in the March sheet. It will return "March 2013."
3. Enter your formula into a cell in the April sheet. It will return "April 2013."
3a. Go back to the March sheet. It now says "April 2013."
4. Fix the error by re-entering the formula into the March sheet. It now says "March 2013" again.
4a. Go to the April sheet. Now it also says "March 2013."

The longer formula posted earlier in the thread does not do this.

hi mate you are right

 

[Aladin Akyurek]

This formula doesn't work for files with multiple sheets/tabs. For example, if you're keeping a monthly tracker, with the months as Sheet Names (March 2013, April 2013, etc.), and you put this formula on every sheet, it will show the same month on every sheet (the month of whichever sheet you entered the formula in last). So if you add the formula to the March 2013 sheet, all of the other sheets will also change to March 2013.
...

It should be achored like the one PaddyD suggested...

=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")

 

[sarfaraz78615]

Hi,
Just a quick one.. tried the archives, but can't seem to find the answer..
How do I get the current sheet name using formulas instead of vba??
Thanks!

There is the Answer by using Simple formula to get the Active Sheet Name

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

 

[ukmikeb]

There is the Answer by using Simple formula to get the Active Sheet Name

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

Thanks for replying to this thread. Actually, the solution was found back in April 2013.

However, your solution fails to work correctly on a workbook with multiple sheets -
refer back to Posts #7 through #10.

 

[Maryam]

there will be shorter ways:

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

Change A1 to $A$1, then it works automatically for as many sheet as you have in your excel file.

 

[Rickity2005]

$A$1
will get it done

 

[myespooir]

Hi Guys, Just curious whether you have a formula to get the name of another sheet rather than the active sheet.

Cheers,
Christina

 

[Aladin Akyurek]

Hi Guys, Just curious whether you have a formula to get the name of another sheet rather than the active sheet.

Cheers,
Christina

In A1 of Sheet1 enter:

=Sheet2!$A$1

In A1 of Sheet2 enter:

=REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"")

 

[joeyjoejoejrshabidu]

You can get the file path, workbook name and sheet name using the CELL function with a bit of text parsing.

For the file path:


=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-2)
For the workbook name:


=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)
For the sheet name:


=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))