Vlookup in dynamic workbooks

[Leandrial]

Hi

I am trying to get a Vlookup funktion to work in a pricing worksheet.

The sheet is going to be a template, so the customer account number will change with every use.

For each item, i need to lookup a discount value in a pricing file.. The file is named as the account no, ex. "3720.xlsm" (account number 3720)

But next use the account number can/will be something else.

The accountnumber is always set in a cell as a value (numbers as text)

All the pricing files are located in the same folder, so only the part before the ".xlsm" needs to change

I an trying this

=VLOOKUP(D8;'C:\Partner Project Shared\Excel rabatfiler\3720.xlsm'!lNettoPriser;2;FALSE)

"D8" is the komponentno. i want to lookup in the discounttable in the pricing workbook

"3720" is the part i need to change, depending on the value in cell C2 (next use i might enter "3536" in this cell, and then i need it to find the file "3536.xlsm" and do the lookup in there)

Ive tried looking around, and all i can come up with is something with the use of the INDIRECT function... but i cant really get that solution to fit my need...

Ive tried something like " & C2 & ".xlsm etc.... but that wont work for me... there is some small thing that i am missing...


Any1 that can help me with this??


Best regards

Kasper

 

[PhilS2520]

Hi Kasper,

Hum! I am not in front of my computer so this might not be 100% correct.

You can try and use the CONCAT() function to build the path to the workbook. You said all the workbooks are in the same location, so =CONCAT(THE WHOLE PATH UNTIL THE WORKBOOK NAME WITH THE LAST \, THEN THE VALUE OF D8, THEN THE REST OF THE PATH AS YOH HAVE IT)

I hope this works. I am pretty sure it should since the path is text. If not you can use the INDERECT() to convert D8 into a reference. But I’m not sure you need this.

 

[Leandrial]

Hi Phil

Thx for the reply

Ive tried, but still cant get it to work

When I try to include the concat in the vlookup function, like this

=VLOOKUP($C$2;CONCAT("'C\Partner Project Shared\Excel Rabatfiler\";C4;".xlsm'!lNettoPriser");2;FALSE)

i get a #VALUE ! error

I then tried to do the Concat in a separat cell, and the do the Vlookup with an INDIRECT to this cell.. like this..

=CONCAT("'C\Partner Project Shared\Excel Rabatfiler\";C4;".xlsm'!lNettoPriser")

and

=VLOOKUP(C2;INDIRECT(G6);2;FALSE)

where the G6 is the cell with the CONCAT function... The CONCAT gives me a textstring in G6 that looks correct.. But when used with the INDIRECT, it returns #REF !

As a note, the cell references in these ex are different then in my original post... this is due to me trying out in an empty sheet with... but my guess is that any1 that will be able to help me, also will be able to distinct between wich cells does what


Any other thoughts on the matter, Phil (or any1)??


Best regards