Using sheet name of workbooks
 

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?

Using sheet name of workbooks
 

=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?