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?