Running vblookup between two .xlsm documents

[D_Rennie]

Dear Gurus.

I have two workbooks one names "Quote" other named "product supply".
In "Quote" i would like to have somthink like a Vblookup that on a givin line can enter price's, disciptions and other data automaticly on that line, From the "Product Supply".
Example
"Quote" A:1 Dropdown list. Select product, Then refers to "product supply" of that product, then trasphers data into "Quote" A:2

[NBVC]

Something like

=Vlookup(A1,'[C:\mydocs\product supply.xlsm]Sheet1'!$A$1:$X$100,2,FALSE)

change path to reflect actual path.

change sheet name and ranges to suit.

This formula looks in column A of table in A1:X100 and takes value from column 2 of the table where value in column A matches A1 of your active workbook.

[D_Rennie]

Thankyou for the responce.
That looks like what i need. I cant seam to figure it out.
Would you be kind enough to do a demostration that relates to my documents.
On "quote" cell B:17 Lookup "product supply" (use prices from colum N)
enter price on quote cell H:17
Im sure I could get it in seconds not hours if i could see in being used.
Cheers

[NBVC]

What exactly are we looking up in the Product Supply workbook.. I don't notice any related data?

[D_Rennie]

My fault Ive posted the worng copy of "product supply"

If i could have a dropdown list on the "quote" from cells B16:B37 or manually enter A product code (refer to "product supply" cells B8:B11)
Then on "quote" cell range H16:37. A Price is then entered from "product supply" colum N.

Example "quote" cell B16 = CL6, enter price from "product supply" cell N8 into "quote" cell H16.

Please not With clicking the print and preview buttion in "quote" a file will be saved into the C: folder.

[NBVC]

First, if you want to enter anything other than what is in the drop down, you will have to disable Error message.. to do that go to Data|Validation and in the Error Alert tab, uncheck "show error alert after invalid data is entered"

Second, the data in the Product Supply column B has extra spaces at the ends.. please get rid of them...

Now, while the Product Supply book is opened, enter this formula in H16:

=VLOOKUP(B16,'[Product Supply-1.xlsm]Sheet1'!$B$8:$N$11,13,0)

adjusting range to suit.

copy down...

Once you close the Product Supply workbook, the formula will automatically add the path...

[D_Rennie]

Thankyou Works well apart from one thing.
If product list "quote" (colum B) is blank it inserts a price of 6.11 from the product supply.
Cant thankyou enough.

[NBVC]

Then add to formula...


=IF(B16="","",VLOOKUP(B16,'[Product Supply-1.xlsm]Sheet1'!$B$8:$N$11,13,0))

[D_Rennie]

Thankyou that worked just like i need.
Thankyou.
Thankyou.