Hands-on: Samsung Galaxy S8 review
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!!
Sorry, that wasn't clear at all.
In Sheet2 B1 I have the formula
Sheet2 A1 contains the text C7 - the formula returns the value of C7 on Sheet1.
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?
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.
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.
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.
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
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
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?
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.
I've done this...
A1 contains =B1
B1 contains Lee
C1 contains =INDIRECT(A1)
C1 displays #REF!
Then I'm stuck, sorry!
This thread is now locked and can not be replied to.