Create a function that returns the list of sheet name(s) in the workbook = SHEETNAME(), =SHEETNAMES()
1. provide a function that returns the current sheet name
2. provide a function that does the listing of the sheets in the workbook. This is presently being done using macro. there are certain companies which do not permit the running of macros within the excel workbook.
Nic Houslip commented
Or how about entering " =(tabname)
Yes Ed that would be quite elegant too
Ed Hansberry commented
Actually just need another argument for the =CELL() function. =CELL("Sheetname",A1)
A.C. WILSON commented
I do this now with an unpleasantly complex work-around, based on parsing the results of =CELL("filename",<current-cell>). Give us the new function, please.
Re conversation about "2."
I myself only use INDIRECT() as a last resort, because it's so obtuse. I need the guts of my Excel sheets and files to be understandable to anyone in my large organization. Ditto for OFFSET() and array formulas. And, I won't use user-defined or third-party functions at all.
Jan, it's not what I "believe" to be bad practice. It just is. My favorite explanation of this can be found if you Google "Volatile Excel Functions Decision Models".
You are trying to make a point by saying you have 43 sheets, but that's only confirming how terrible the spreadsheet is designed. As you continue to develop your Excel/data skills, this will become much more apparent. I'm simply suggesting to anyone reading that they avoid the mistakes I made early in my career.
That being said, I know people find volatile functions as an easy stop-gap measure when they're not aware of their alternatives. That's perfectly fine until you start dealing with truly complex models. However, Microsoft should do a better job educating their users, not continue to support bad habits by expanding these workarounds.
Your first example has several other alternatives.Give me an example spreadsheet containing an example of when you think INDIRECT is necessary and I will show you a better alternative. In doing so, it will be faster, more reliable, and more dynamic.
Corey - you're not clear on why volatile functions are bad - they are just functions.
If life was only as simple as a single table for data :)
The reason to use indirects are multiple and functional. For example I do earned value and I need to sum from the current week to the end of the project. Next week the week has moved if you get my drift, but the indirect doesn't care, picks up the right starting point. Thus I can have formula which are regular and don't need exceptions. It is exceptions and difficult formula which are bad practice :)
Other examples: I have a a test workbook here which semi automates data entry for testing. From the master list I have 43 sheets with on each some 200-500 test steps. Loads of indirects() sheetnames() and VBA :) Works like a dream
Then I have a spreadsheet which generates some 1,000,000 lines of data using 100's of input configuration sheets again with lots of indirect()s to know where the data starts and stops. Works fine too, just wish sometimes we could set it to 10,000,000 rows. Big enough for you?
Let's appreciate the diversity of functions in Excel, how we use it is up to people, not what you believe is bad practice. What I find so good is that it doesn't break.
Using volatile functions is bad practice. They are never the best solution. Sometimes they are the simplest, so for small, simple workbooks they're fine. If you have not noticed performance issues with INDIRECT then you don't deal with any large or complex spreadsheets. I've seen several people design these dynamic sheet name spreadsheets you're speaking about and I just shake my head. Data should be stored in a single table with a column to specify the attribute you're filtering on (as opposed to a separate sheet). Development should not focus on supporting bad habits.
Corey - so what that they are volatile - and yes indirect() is exactly why we want it :). It allows to dynamically build what one is wanting to retrieve.
As for performance, I don't share that concern, I am an extensive user of indirect() and it does not give any noticeable performance issues. With sheetname() the user can change the sheetname and using the indirect you can then build dynamically growing workbooks.
Not sure if someone mentioned this before but another issue with these functions is that they are volatile, meaning no one should actually use them in a large file. Also, I'm concerned this would lead to more people using INDIRECT, which is never a good thing.
Harlan Grove commented
Alternatively, make ALL informational XLM functions proper worksheet functions since GET.WORKBOOK(1) returns a horizontal array of all worksheets in the active workbook and GET.DOCUMENT(1,GET.CELL(66,A1)) returns a horizontal array of all worksheets in the workbook containing this formula.
@Tony Valko: Especially annoying since Lotus 1-2-3 Release 3's (1989, so 27 years ago) @CELL could return the worksheet name, its @INFO could return the number of worksheets in a workbook, and a combination of @CELL, @COORD and @@ could be used to return all worksheet names in a workbook. I believe Quattro Pro also had this functionality in the early 1990s. OTOH, Excel requires VBA or XLM functions.
Mike Martens commented
Tony valko commented
This is a "no brainer".
Excel has been around for decades yet we still don't have a SIMPLE worksheet function to return the sheet name.
Create a simple function that returns the worksheet sheet name of a cell.
e.g. = worksheetname() would return the worksheet name of the current cell. Could work across worksheets so you could select a cell in another worksheet.
This can currently be done without VBA using a long formula =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255) but it a little daunting for beginners as a formula and can be unstable.
Could additionally extend functionality to workbooks name etc.
Kenneth Barber commented
I should mention that the current workaround is
Not pretty. I also agree that it should not be called ShtName. SHEETNAME is more appropriate.
Like the idea, hate the name. Please not shtname
Brett Ables commented
This would also help shorten the formula for dynamic hyperlinks to other cells in the same workbook using the hyperlink() function... though really the hyperlink function should just have optional sheet and range fields instead of needing the entire filepath to link to 10 cells down.
of course one can calculate it, but it would be so handy if you could get the sheetname from a cell-reference without the hassles