vlookup on different sheet/tab based on stored variable

[jat]

good day,

need help with indirect vlookup formula that i cannot figure out.

File1 is where the macro runs and has several tabs (Sheet1, CLI, COM);
macro button runs from Sheet 1. i use it to import a flatfile or a textfile
to generate orders. when the macro runs, it opens the flat file "File2",
starts to format the file so that i can use it in excel and i have it save
the supplier code in a variable from this - SupCode = Range("B4").Value
- in this example, the SupCode holds the value. (when the macro stops
later, i can go into the code, and hold the cursor over the SupCode and it
shows "CLI")


the extra rows and columns are removed and i am left with an item number,
quantities, and prices. what i want to do is do a vlookup formula that will
be in File2 to lookup a price in File1, with the Sheet being the stored value
in SupCode. in this example File1 and the tab being CLI;

the followign formula works
=VLOOKUP(A3,'[Order Generator.xlsm]CLI'!$B:$D,3,FALSE)
works but i want to automate the CLI portion to be dependant on SupCode.

any ideas?

 

[Bernie Deitrick]

jat,

Do you want a cell with a formula?

ActiveCell.Formula = "=VLOOKUP(A3,'[Order Generator.xlsm]" & SupCode & "'!$B:$D,3,FALSE)"

HTH,
Bernie
MS Excel MVP

 

[Patrick Molloy]

this is similar to the concatenate question posted a little later by KG Old
Wolf

use the INDIRECT function and concatenate the texts
so break the formula into bits, you'll see from the following ...

=VLOOKUP(A3,'[Order Generator.xlsm]CLI'!$B:$D,3,FALSE)
=VLOOKUP(A3,INDIRECT("'[Order Generator.xlsm]CLI'!$B:$D"),3,FALSE)
=VLOOKUP(A3,INDIRECT("'[Order Generator.xlsm]" & "CLI" & "'!$B:$D"),3,FALSE)

B1 := CLI
=VLOOKUP(A3,INDIRECT("'[Order Generator.xlsm]" & B1 & "'!$B:$D"),3,FALSE)