XL07 create data validation list containing the names of each shee

K

ker_01

I am helping a colleague who uses XL2007. Data reports are downloaded from a
separate application, and each update is pasted into a new tab in Excel.

Since each new tab contains all historical data, I need to make it easy for
the summary sheet to reference the most current worksheet. The names are long
and varied, so I thought the best approach would be to have a data validation
cell that lists all active worksheets, and use the selected value to update
the formulas by using 'Indirect'.

The following code snippet is the closest I've found- it gives the tab name
for the selected cell. However, this doesn't automatically bring in the name
of a newly added sheet (unless the formula was updated to reference a cell on
that sheet, which doesn't ensure the level of simplicity I'm seeking)

=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))

So, there is probably a better approach, if this is possible at all. What is
the best approach (without resorting to VBA) to create a dynamic list of all
of the worksheet names, in XL07?

Thanks!
Keith
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top