VLookup into diffrent worksheets in a diffrent workbook

**joffy1979** · 05-25-2013, 07:51 AM

Hi people,

I have a problem i hope someone can help with. I have a sheet which has a list of names in a workbook names.xlsm in column B and the following formula in column G:

=VLOOKUP($G$1,'[Web Data.xlsm]Paul Smith '!$P$1:$AH$102,19,0)

This works perfectly well however the workbook Web Data.xlsm has almost 100 different worksheets with the names in names.xlsm column B. So i was hoping there was a way not to do this 100 times with all the diffrent names.

So Paul Smith is B2 in names.xlsm and there is a worksheet in Web Data.xlsm called Paul Smith. The in B3 I have Ben Smith and a worksheet in Web Data.xlsm called Ben Smith. So i was hoping there is a macro or simple way to reference the worksheet in Web Data.xlsm with the information in names.xlsm column B.

**JBeaucaire** · 05-25-2013, 09:37 AM

1) Location: WHYDOYOUNEEDTHIS

This forum is used worldwide. Having the city/region appear helps us communicate more informatively right out of the box. The versions of Excel have small differences from country to country, and knowing someone your talking to is on the other side of the world might make everyone more patient with long delays.

Please update your profile to properly display a usable "Location". Obviously, we don't want your address.

=====================

2) The function you're looking for is INDIRECT(). Read up on that function in the Excel Help files. It is used to construct an address using pieces of text, then converting the resulting string into a usable link/address for use in a formula.

Good News - it does exactly what you want.

Bad News - it does not work for creating links to external workbooks unless those workbooks are open, too. But it works just fine if that external workbook is open.

Maybe Good News - you can add new functionality into your Excel to give you an additional set of tools, one of which is a replacement function for INDIRECT() that does work on closed external workbooks. This Add-In is called MOREFUNC and the new function would be called INDIRECT.EXT()

Here's a link to a thread here on the forum discussing this.

Maybe Bad News - even though it works once installed and in use, if there are a LOT of said formulas, you will most likely start to see performance lag in your workbook.

**Tony Valko** · 05-25-2013, 09:51 AM

Originally Posted by JBeaucaire

Here's a link to a thread here on the forum discussing this.

Even more bad news...

None of the links in that thread lead you to the add-in anymore.

I don't know where you can find it at this time.

**JBeaucaire** · 05-25-2013, 10:07 AM

Tony, thanks for that! That reference page has been updated. The new link to d/l page:

http://www.ashishmathur.com/tag/morefunc/

**Tony Valko** · 05-25-2013, 11:48 AM

The author of the morefunc add-in is Laurent Longre, a (former?) MS MVP.

It's good that Ashish Mathur (another MS MVP) has a working link to the add-in.

**JBeaucaire** · 05-25-2013, 11:51 AM

Hah, shows how much I know....

Updated.

**joffy1979** · 05-26-2013, 06:46 AM

Thank you for the advice, however i am unsure how to use it. Do i incorporate the INDIRECT into the VLookup or do i needs to set up arrays in all the 100 odd worksheets then just use the =INDIRECT?

I am totally confused how to implement the advice in the given formula. I might not have explained what i needed well enough. The second work book is called Web Data the variable is Paul Smith, which is a sheet in the Web Data workbook.

=VLOOKUP($G$1,'[Web Data.xlsm]Paul Smith '!$P$1:$AH$102,19,0)

Your help is appreciated.

**joffy1979** · 05-26-2013, 07:16 AM

Ok I think i have it

=VLOOKUP(H1,INDIRECT("'[Web Data.xlsm]"&$B$2&"'!$P$1:$AH$102"),19,0)

Does this look right? seems to work

**JBeaucaire** · 05-26-2013, 09:16 AM

Yes, that is the correct way to do it if the Web Data.xlsm file is open.

**Tony Valko** · 05-26-2013, 11:35 AM

Originally Posted by joffy1979

Ok I think i have it

=VLOOKUP(H1,INDIRECT("'[Web Data.xlsm]"&$B$2&"'!$P$1:$AH$102"),19,0)

Does this look right? seems to work

As JB noted, that will work as long as the source file is open or, if the source file is closed until the file with the formula does a calculation.

Quoted range/cell references within the INDIRECT function will automatically be evaluated as absolute references so there's no need to include the dollar signs:

=VLOOKUP(H1,INDIRECT("'[Web Data.xlsm]"&$B$2&"'!P1:AH102"),19,0)