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