Help needed with Vlooup from Dynamic workbooks...

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 am trying this

Please Login or Register  to view this content.

"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

Please Login or Register  to view this content.

that turns out a #VALUE! error

and

Please Login or Register  to view this content.

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 !


But nothing i tri will work for me..


Any1 that can help me with this??


Best regards

Kasper

Originally Posted by than_gold

Try with Iferror + Vlookup

Formula:

Please Login or Register  to view this content.

https://exceljet.net/formula/vlookup-without-na-error

Thx for the reply.... but im not after the error handling... im after the function actually being able to do the Vlookup in the correct file...

but i cant get it to accept the account number as part of the filename for the workbook to access..

best regards