Reference a link to new document via a cell

**Rivermax** · 03-08-2010, 11:51 AM

Hello,

I would like to use a vlookup formula to pull data from multiple sheets (circa30) the cell is the reference data is the same in each sheet. I can use a standard vlookup to do this ie:

=VLOOKUP($I$4,'U:\mydocs\work\[colours.xlsm]Sheet V1.1'!$B$2:$F$11,5,FALSE)

But what I would like to do is create a formula with the syntax:

VLOOKUP($I$4,$A4,5,FALSE)

Where $A4 would be interepreted as "'U:\mydocs\work\[colours.xlsm]Sheet V1.1'!$B$2:$F$11"

This would enable me to replicate across many rows and save time... hopefully this makes sense - does anyone know if and how this can be done?

**JBeaucaire** · 03-08-2010, 02:00 PM

That would require use of the INDIRECT() feature.

=VLOOKUP($I$4,INDIRECT($A4),5,FALSE)

But the problem is that INDIRECT() references have to be to OPEN workbooks if you use the native INDIRECT() feature.

Here's a forum page explaining the MoreFunc addin which would give you the ability to reference closed workbooks indirectly.

http://www.excelforum.com/excel-gene...ndirect.html#2

**Rivermax** · 03-09-2010, 06:15 AM

Thanks that's what I was after.

I looked at using the indirect.ext but this seemed to function in the same fashion as indirect ie will only work with open sheets.

Would be really useful to have a simple code to pull from closed sheets!

**JBeaucaire** · 03-09-2010, 06:18 AM

The reason INDIRECT.EXT() exists is to pull from closed workbooks, that what it provides. Check it out more closely.

======
If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

**Rivermax** · 03-09-2010, 08:38 AM

I know it is meant to do that but on some pcs it just doesn't seem to function properly, if it works for you you are one of the lucky ones - do a simple google search to see the number of users who cannot use this functionality...

**JBeaucaire** · 03-09-2010, 01:20 PM

Ah, well, there you go. I've never used it as the second parameter in a VLOOKUP(), but that apparently is where it fails altogether. INDIRECT.EXT() works, but for returning a single cell reference, not a range, and you need a range. So you're right, won't work for you. (source)

Learn something new around here every day...hehe.

==========
So, the only other idea I would have is using SEARCH/REPLACE to put a different reference directly into your formulas.

You could still put the reference strings into a cell and then use a macro to create the formula for you and insert it into the column needed.

**Rivermax** · 03-09-2010, 02:59 PM

Thanks JB - what I have ended up doing is creating a new book which houses a summary sheet from all the workbooks I need as values from which I can leave open and pull via vlookup and indirect.