vlookup table_array option

[blackduck]

I would like to replace the workbook reference (ie.Site019) and point it to a cell reference which has this name in it.

My formula in cell F15 is:
=+VLOOKUP(D15,Site019.xls!tblSite_Input,2,FALSE)

My heading for cell F14 is Site019

I was thinking of something like =+VLOOKUP(D15,F14 &.xls!tblSite_Input,2,FALSE) but I dont know the correct syntax and also whether it is possible anyway.

Thanks
K

 

[xlbo]

this would be better placed in the Micorsoft Office forum. However, the answer is to use th INDIRECT function. Have a look in the help file and post back if you have any specific issues with it.

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[blackduck]

thanks xlbo,

I checked out the INDIRECT function but dont think its what i'm after.

I should reword my question to be eg.Site019.xls because I will actually have about 25 columns of sites and the numbers are not consecutive.

All the rows under each site contain formulas to get specific data from the separate worksheets.

My headings in cell F14, F15, etc are all drop down lists, so for example in cell F14 I may pick Site054 for example.

ps. sorry for posting in wrong forum, also read through faq link you gave me and added microsoft links to favorites, will use heaps.

cheers
K

 

[xlbo]

unless your requirements have changed, INDIRECT most certainly is what you need:

With Site019 in cell F14, enter

=VLOOKUP(D15,INDIRECT(F14 & ".xls!tblSite_Input"),2,FALSE)

to see what I mean

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[blackduck]

xlbo

It works - thanks for spelling it out for me.

Read some other references to indirect besides the microsoft one and am getting my head around it now.
For other - dont forget to have the corresponding sheet open otherwise you get the #REF error.

Thanks heaps.
cheers
K.

 

[GlennUK]

If you want to use INDIRECT with the other book closed use INDIRECT.EXT instead, which is part of Laurent Longre's free MOREFUNC add-in ( which has loads of other useful functions too ), from here:

http://xcell05.free.fr/english/

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[blackduck]

I have downloaded the INDIRECT.EXT function and used the following:

=VLOOKUP(D15,INDIRECT.EXT(F14 & ".xls!tblSite_Input"),2,FALSE)

This works, but only with the other book open still. The workbooks are in the same directory.

thanks again
K