there will be shorter ways:
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))
This is a discussion on Get Sheetname in Formula within the Excel Questions forums, part of the Question Forums category; Hi, Just a quick one.. tried the archives, but can't seem to find the answer.. How do I get the ...
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 will be shorter ways:
=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))
I don't know about the current sheet name but here is how you get the active sheetname:
SheetName=Activesheet.Name
"...using formulas instead of vba" !
@YasarShazad
Thanks.
Something I've been looking for too.
Mike
-----------------------------------------------
Some solutions don't require an IF!
When posting code wrap your code between [CODE] tags.
Paste your Excel data...
MrExcel HTML Maker or Excel Jeanie
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 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.
Last edited by Chasden; Apr 12th, 2013 at 03:13 PM.
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.
Want better/faster responses to your questions?
Use Excel Jeanie to post samples of your sheet.
The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.
Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
Ferris Bueller A.K.A. John Hughes, 1986
Like this thread? Share it with others