Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup on different sheet/tab based on stored variable
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup on different sheet/tab based on stored variable
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 "jat" wrote in message ... 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
vlookup on different sheet/tab based on stored variable
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) "jat" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XL2002 - VLOOKUP with variable Sheet Name | Excel Worksheet Functions | |||
set a variable based on a vlookup | Excel Discussion (Misc queries) | |||
Macro with vlookup with variable sheet | Excel Programming | |||
Copy rows to new sheet based on variable cell reference | Excel Programming | |||
Sheet scroll based on variable | Excel Programming |