0

I'm developing a log that has Quote IDs and I want to automatically extract customer information when the Quote ID is entered into each row.

Unique worksheets are created in a folder that is named by the Quote ID. I used INDIRECT function but works only when that specific worksheet is open. It defeats the whole purpose. I used the MOREFUNC add-in but the performance of the log significantly drops.

I would ideally prefer a VB function when a Quote ID is entered into row B, the function would search for that specific worksheet and extract the customer info.

enter image description here

Community
  • 1
  • 1
Bikerbash
  • 1
  • Have you tried using `ExecuteExcel4Macro` to retrieve data from closed workbook? See [here](http://stackoverflow.com/questions/9259862/executeexcel4macro-to-get-value-from-closed-workbook/9261915#9261915). – David Zemens Oct 06 '16 at 16:22

1 Answers1

0

Are you just wanting something like a Vlookup? Example below of a vlookup that looks into a closed workbook. 

 =IFERROR(VLOOKUP(B8,'C:\Users\Admin\Documents\Quote ID\[workbookname.xlsb]SheetName'!$B:$M,2,FALSE),"")

FullPath[workbookname.xls]sheetname'!range

The easiest way i like to write them. Open both workbooks in the same Excel session and it will allow you to write the vlookup you want. Then close the workbook you want to look into and excel will auto fix the vlookup to reference the closed book.

Hope that helps

Ricky
  • 98
  • 4