Linking Spreadsheet to fill in PO Form

[TheLaxMom]

I need to see if this is something that can even be done.

We have a Workbook that has item numbers and the wholesale cost of the item. My boss would like to have a PO created to do the following:

Blank PO
Type in Item Name in a cell and have an alternate cell fill in with the wholesale cost of that particular item
Calculate the total cost of the line item when a quantity is added into an additional cell

I imagine it would have to contain a complicated formula if it can even be done at all.

Does anyone have any suggestions on how to make this happen or an alternate way to do something similar?

[djauncey]

It certainly can be done, you would need to use a vlookup formula to make it pull in the correct data based on the Item number

If you could upload an example document; with say 5 products with their info then I could sort out a formula to do that for you

[TheLaxMom]

Book3.xlsx

Here is part of the sheet I am working with.

That's the one I need to get the data from - I know I can create the one to get the data to.

[djauncey]

example.xlsxOkay so I assume that you want to type in the product code (column C) and then have the data brought up?

I'd use this formula in column B onwards

=if(isblank($A1),"",vlookup($A4,'Misc. (2)'!$C$9:$K$1000,2,TRUE))

Because of the way vlookup works you have to have the code as the first column; hence starting the lookup in C not A. To then change the columns just change the number before " ,TRUE))"

Have a lookup at sheet2 for an example.

Hope this helps

[TheLaxMom]

That's correct. as well....he would like the platinum price to be thrown into another field when he chooses the product. Will that VLook Up be all inclusive?

[djauncey]

Yeah, the vlookup is looking up A4 in the entire Misc. (2) sheet and then pulling column 2 back.

vlookup( this value, from this data, this column, True)

If you want to bring another column in just copy the fomula and change the number 2 to the new number; so for the platinum one put =if(isblank($A1),"",vlookup($A4,'Misc. (2)'!$C$9:$K$1000,9,TRUE))