I have an Excel 2003 workbook which contains a master sheet that needs
to retrieve and use the names of the other sheets contained in the
workbook. For example, the master sheet ("Instructions") would show in
cells A1 - A5 the names of the five other sheets in the workbook that
might be called "Test1", "Test2", "Test3", "Test4" and "Test5". Once
the master sheet has a usable reference to the other sheet names, any
time one of those names changed (or additional sheets added) the master
sheet would automatically get updated. Is this possible either using a
sheet reference formula or VBA (preferably formula)? Ideally, the
master sheet would reference the other sheets as a Hyperlink cell so
clicking on any of the individual names would take the user directly to
the appropriate sheet!

Any ideas how this can be done?

=MID(CELL("filename",Test1!A1),FIND("]",CELL("filename",Test1!A1))+1,
LEN(CELL("filename",Test1!A1))-FIND("]",CELL("filename",Test1!A1)))

set up a formula for each sheet. This will adjust if there is a change.
Assume the above formula is in A1, then in B1 (as an example), you can buid a
hyperlinke using the hyperlink worksheet function

=HYPERLINK(MID(CELL("filename",Test1!A1),FIND("[",CELL("filename",Test1!A1)),255)&"!A1",A1)

These will adjust when the sheet name is changed.

--
Regards,
Tom Ogilvy




"Marcus T" wrote:

I have an Excel 2003 workbook which contains a master sheet that needs
to retrieve and use the names of the other sheets contained in the
workbook. For example, the master sheet ("Instructions") would show in
cells A1 - A5 the names of the five other sheets in the workbook that
might be called "Test1", "Test2", "Test3", "Test4" and "Test5". Once
the master sheet has a usable reference to the other sheet names, any
time one of those names changed (or additional sheets added) the master
sheet would automatically get updated. Is this possible either using a
sheet reference formula or VBA (preferably formula)? Ideally, the
master sheet would reference the other sheets as a Hyperlink cell so
clicking on any of the individual names would take the user directly to
the appropriate sheet!

Any ideas how this can be done?