using a formula to create an ordered purchase order

[Jesse]

I use excel to send out request for quotes to vendors and then transfer that
information into a purchase order. I send out a request for quote to several
vendors for the same products. When vendors return the request I open an
excel spreadsheet and put the prices in each vendors spreadsheet. I've
created a spreadsheet that allows me to put an "x" beside the cheapest price
and an excel formula coppies the description and the price to a different
purchase order spreadsheet. =IF('RFQ Results'!D8="X",'RFQ 1'!D18," ") The
problem arises when I purchase items from the same request for quote from
different vendors. The way the formula is written when I put an "x" beside
line items 1, 4, and 7 on the request for quote they are coppied to lines 1,
4, and 7 on the purchase order instead of lines 1, 2, and 3 on the purchase
order. I need a formula that allows me to select lines 1, 4, and 7 from the
request for quote page and places the itme in lines 1, 2, and 3 of a purchase
order sheet. Does anyone know how? Thanks

 

[dan dungan]

you can use nested ifs

 

[Jesse]

Can you give an example of a nested if formula that would work?

 

[dan dungan]

I would need more specific information, like:

Which spreadsheet holds this formula?
=IF('RFQ Results'!D8="X",'RFQ 1'!D18," ")


Show these specific formulas:

The way the formula is written when I put an "x" beside

line items 1, 4, and 7 on the request for quote they are coppied to
lines 1, 4, and 7 on the purchase order instead of lines 1, 2, and 3
on the purchase
order.

 

[JLatham]

May also want to ask how many items might need to be transferred in total.
Keep in mind the limit of 7 levels of nesting in a formula. If there's going
to be a need for more testing than that, it may be required to look to a VBA
solution.

 

[Max]

You might to try this sample from my free archives for some relevant ideas:

http://savefile.com/files/1267592
Auto-Ordering to Diff Vendors.xls

It's a pure formulas driven play which dishes the order lines out
automatically from a mastersheet to various vendor sheets
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik