I have a workbook with a bunch of hyperlinks and I've noticed that
when I re-name the workbook, the hyperlinks no longer work because the
workbook name is no longer 'correct. So, I plan to use the INDIRECT
function to make some dynamic links. BUT, I'm finding this process
very difficult ...

Here's what I have:
sheetname: ADMIN
cell R1: workbook name created by formula
eg. [Comprehensive Hours tool.xls] 'includes the []
cell P2:P66: sheet names
eg: Main Dashboard

I'll use a Workbook_BeforeClose macro to re-calc the workbook name
(and thus the hyperlinks).
how would I write the formula that will use these values to build the
HYPERLINK .... assume that all hyperlink should go to A1 (on
destination sheet) and 'friendly name' should be the sheet name

Thanks,
ray