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!

 

[PaddyD]

there will be shorter ways:

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

 

[mjbeam]

I don't know about the current sheet name but here is how you get the active sheetname:

SheetName=Activesheet.Name

 

[PaddyD]

"...using formulas instead of vba" !

 

[YasarShahzad]

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!

Use below formula anywhere in the sheet to get the sheet name.

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

 

[ukmikeb]

@YasarShazad

Thanks.

Something I've been looking for too.

 

[Chasden]

Use below formula anywhere in the sheet to get the sheet name.

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

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.

there will be shorter ways:

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

This one works as intended, with a different month on each sheet.

 

[YasarShahzad]

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.



This one works as intended, with a different month on each sheet.

I tried and it works just fine

 

[Chasden]

I tried and it works just fine

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.

 

[Jonmo1]

This
CELL("filename")
Is a Volatile function, it recalculates every time anything is changed in the book.
Any time anything changes on any sheet, then Cell("filename") is recalcuated (on ALL sheets)
But it returns the sheet of whichever sheet is currently active to ALL occurances of the Cell("filename") function.


By changing it to
CELL("filename",A1)

That "Ties" the Cell function to a cell on that sheet.
So each one on each sheet is dependant on the A1 in it's own sheet.