Excel 2002 VLOOKUP help

  Leepy Lee 06:45 09 Jun 2007
Locked

Wonder if anyone can help me with this please.
I'm using this formula in Excel 2002 and it works OK.

=VLOOKUP(B5,'J:\Lee\House\Cabling Records\Master Cabling Record.xls'!FFWW2,2,FALSE)

I need a way of automatically changing the "FFWW2" part of the table array, by inserting the contents of a cell from the current sheet.

E.g. cell M12 has a label "From", the contents of which, change. I want the contents of "From" to be automatically inserted to replace "FFWW2"

I've tried putting the cell name in place of "FFWW2", putting another formula enclosed in quote marks which points to the contents of "From", putting the actual sheet name & cell name in the formula. Nothing works.

Is there a way of doing this?

Any help with this may prevent the loss of the last bit of hair I have!!

Many thanks.

Lee

  VoG II 09:35 09 Jun 2007

I'm assuming that FFWWW2 is a named range.

You could try using INDIRECT.

=INDIRECT("'sheet1'!"&A1)

returns the value of A1 on sheet1.

You may need to experiment to get the syntax right.

  VoG II 09:39 09 Jun 2007

Sorry, that wasn't clear at all.

In Sheet2 B1 I have the formula

=INDIRECT("'sheet1'!"&A1)

Sheet2 A1 contains the text C7 - the formula returns the value of C7 on Sheet1.

  Leepy Lee 08:47 10 Jun 2007

Thanks VoG for the suggestions, but the problem seems to be that the table array part of the VLOOKUP function does not like having a sheet name plus a cell name from another sheet. I still can't replace FFWW2 with different text using INDIRECT.

Anyone else any ideas please?

Lee

  daba 21:43 10 Jun 2007

Is your lookup in another workbook, or the same workbook on another sheet ?

I have got this lookup to work fine (Excel 97, so later should be OK), but only if the lookup is in the same workbook. It works because there is no need to specify "target sheet!target range" because the name given to the lookup array is global for the workbook.

=VLOOKUP(A2,INDIRECT(B$1),2,FALSE)

I can lookup into different lookup tables by putting the name of the array in B1 directly, or indirectly, and even if B1 contains a formula.

  Leepy Lee 17:40 12 Jun 2007

Hi daba

The table array is is another workbook. I've tried your suggestion using INDIRECT but it still will not put the contents of a cell into the VLOOKUP function as the table array parameter. I have even put the full path to the other workbook in the cell referenced by INDIRECT, but still no joy.

Cheers,

  daba 01:00 21 Jun 2007

This does work, even into another workbook, and I think I know why you are failing, the other workbook needs to be OPEN when you try to access it. From the Help on INDIRECT.

[quote] If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value. [\quote]

FYI - The correct syntax to use is

=VLOOKUP(A2,INDIRECT("Book2.xls!"&B$1),2,FALSE)

where B1 contains (as a "text" value) the named range of the lookup target. Obviously B1 can be a dynamic cell (i.e. contains a formula). Using names for the data simplifies things tremendously, as I said before.

If you can't sort it let me know, and I can Y/E you the test files I've had success with.

If you need to know how to automate the opening of the other file, when the main file is opened, I'm sure if you ask here, someone will provide you a method to achieve it

  Leepy Lee 19:48 22 Jun 2007

Thanks daba, but this still will not work.

Even if I simply put =INDIRECT(T5,false) or =INDIRECT(T5)in a cell on its own on the same sheet where T5 is, it still returns #REF!

Is this maybe an issue with Excel 97?

Cheers,
Lee

  VoG II 20:06 22 Jun 2007

Try this

In A1 enter B1

Enter anything in B1

In another cell enter =INDIRECT(A1)

It should give you the same value that you entered in B1.

  Leepy Lee 20:40 22 Jun 2007

I've done this...

A1 contains =B1
B1 contains Lee
C1 contains =INDIRECT(A1)

C1 displays #REF!

  VoG II 20:52 22 Jun 2007

Then I'm stuck, sorry!

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

AMD Ryzen news - release date, UK price, features and specifications

The pulp art of 80s computer game magazine covers

Best value Mac: Which is the best £1249 Mac to buy

Comment faire des captures d’écran sous Windows 10 ?