Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Mar 2001
    Location
    Ontario, Canada
    Posts
    57
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have a spreadsheet with many worksheets of "raw" data. The first sheet extracts the data from (one of) the raw data sheets, in a different format. In A1 I type in the name of the raw data sheet that I want to access, and then use Indirect to look up data on the specified raw data sheet.

    I don't like typing, and would like to change A1 into a dropdown list that I can choose from. I can create a list of the current sheetnames, and use that to populate the dropdown list, but I would like to have this list updated dynamically as sheets are added/deleted or have their names changed. I was thinking of using a macro to create the list as an array (rather than having it populate some cells) but need some way of triggering the macro, either when the collection of sheetnames changes or (preferably) when the dropdown arrow for cell A1 is activated.

    Is this possible?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    See the section DYNAMIC WORKSHEET NAME LIST in Excel Formulae Using Worksheet Names in Cells

  3. #3
    Star Lounger
    Join Date
    Mar 2001
    Location
    Ontario, Canada
    Posts
    57
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks Hans. I'll try that. Just at a quick glance though, it seems to alter the list when sheets are added or removed. Is there any event that would be triggered when a sheet is renamed?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='wvdeijk' post='787944' date='07-Aug-2009 18:07']Is there any event that would be triggered when a sheet is renamed?[/quote]
    I don't think so. You might refresh the list in the Workbook_Open event.

  5. #5
    Star Lounger
    Join Date
    Mar 2001
    Location
    Ontario, Canada
    Posts
    57
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Pity, but your suggestion did give me a thought about a possible work-around. In order to change the sheetname on one of the raw data sheets I would have to activate that sheet first. That means that I would have to re-activate the first sheet after that. I'll just refresh the list when I re-activate the first sheet.

    Thanks for your help, as always.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •