Feedback by UserVoice

How can we improve Excel for Windows (Desktop Application)?

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.

106 votes
Sign in
(thinking…)
Password icon
Signed in as (Sign out)

We’ll send you updates on this idea

Anonymous shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

17 comments

Sign in
(thinking…)
Password icon
Signed in as (Sign out)
Submitting...
  • A.C. WILSON commented  ·   ·  Flag as inappropriate

    Re "1."
    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.

  • Corey Becker commented  ·   ·  Flag as inappropriate

    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.

  • Jan Wx, NZ commented  ·   ·  Flag as inappropriate

    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.

  • Corey Becker commented  ·   ·  Flag as inappropriate

    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.

  • Jan Wx, NZ commented  ·   ·  Flag as inappropriate

    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.

  • Corey Becker commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    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.

  • Tony valko commented  ·   ·  Flag as inappropriate

    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.

    =SHEETNAME()

  • Ben commented  ·   ·  Flag as inappropriate

    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  ·   ·  Flag as inappropriate

    I should mention that the current workaround is
    =MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

    Not pretty. I also agree that it should not be called ShtName. SHEETNAME is more appropriate.

  • Brett Ables commented  ·   ·  Flag as inappropriate

    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.

  • Jan Wx, NZ commented  ·   ·  Flag as inappropriate

    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

Feedback and Knowledge Base