Get Sheetname in Formula

John Yazou

New Member
Joined
Mar 17, 2002
Messages
49
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!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
there will be shorter ways:

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))
 
Upvote 0
I don't know about the current sheet name but here is how you get the active sheetname:

SheetName=Activesheet.Name
 
Upvote 0
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")),"")
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
I tried and it works just fine

I tried it, and it doesn't. :p

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.
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top