How to create a dynamic link?

[jfmaster]

Hello All,

I have an issue where I have a file with many links to other workbooks with file names that are based on a persons name. The list of names / people changes fairly regulary - new ones are added and others are removed. My basic formula looks like this:

=+'W:\fnetsc\grponly\Sales Comp 2012\Incentive Compensation\IC Templates\[Doe, John H.xlsx]WtdPerf'!E$13

What I'd like to do is have the formula for the link use a cell value rather than have the individual name embedded in the formula. For example instead of Doe, John H, I want it to use the value in cell A5.

Is this possible? I've searched & searched and can't find anything on this topic.

Your help is greatly appreciated!

[JBeaucaire]

The function you want is HYPERLINK(), this lets you piece together strings to create a clickable hyperlink.

=HYPERLINK("=+'W:\fnetsc\grponly\Sales Comp 2012\Incentive Compensation\IC Templates\[" & A5 & ".xlsx]WtdPerf'!E$13", "Link")

[JBeaucaire]

oops, I misread that. You're talking about a FORMULA that uses strings to create the link to data inside another workbook. The function you want is INDIRECT(), but that doesn't really work on closed workbooks, you can only INDIRECTly reference sheets in teh same workbook or other OPEN workbooks.

If you really must indirectly reference closed workbooks, then you'll have to install more functionality into your Excel program. Only people with the same installed ADD-IN will be able to use your sheet.

The ADD-IN is called MoreFunc...read all about it here:
http://www.ashishmathur.com/tag/morefunc/

See step #4 on that page for instructions on downloading and installing.

Go into TOOLS > ADDINS and activate MoreFunc.

Now you have many, many new functions available to you. Any place you used INDIRECT, now use INDIRECT.EXT instead and it will work on closed workbooks.

[jfmaster]

Thanks Jerry. It took me a while to get the formula to work, but it works great!

One other question on this subject though, one of the file (persons) names is O'Malley (with the apostrophe). Is there a way to make this one work because as it stands, I get the dreaded #REF for that file. I've tried to make it with 2 single apostrophes, but that didn't work and I don't know of other options.

[JBeaucaire]

Just remove the apostrophe from the filename. You don't really need it in the actual filename, right?

[jfmaster]

I could do that, but the list of names we get are from our HR system, and you know HR - everything must be perfect (match to their real names). If you can't think of any alternative, then we'll just have to remember to remove the apostrophe once each month when we do our upload.

[JBeaucaire]

I don't use MOREFUNC nor link to external workbooks.

What would a normal link to this O'Malley workbook look like?

[jfmaster]

like this:

='W:\fnetsc\grponly\Sales Comp 2012\Incentive Compensation\IC Templates\[O'Malley, Shaun - IC Worksheet template.xlsx]WtdPerf'!$E$13

this works just fine.

[JBeaucaire]

It looks like the indirect actually needs to replace the single ' with two separate apostrophes ''

maybe inserting this into your formula:

SUBSTITUTE(B1, "'", "''")

[jfmaster]

I'm off today, so I'll give this a shot 1st thing Monday & will get back to you. Thanks!

[jfmaster]

Well, I wasn't able to get the 2 single apostrophe's to work, so I tried substituting a single space (in the workbook name as well), and it works perfectly. Thanks for your help Jerry!