5 Replies Latest reply: Nov 30, 2012 1:08 PM by twinjayhawks
Level 1 (0 points)

I need to create an order form.

The form will have company A and company B. Both companies will have different prices for each item that they sell. As I go through my inventory each week I need to create a form of things I need to order. I would like to check an item to order from the cheapest company... Then print out all of the items I need to order from each company.

Does anyone know if this is possible?

• Level 6 (11,515 points)

I took a stab at it. Lots of formulas. I'll do my best to explain. I did it all in one sheet to make it easier. You can drag and drop the various tables to different sheets later.

Now for the setup and the formulas:

"Company A Price List" and "Company B Price List" tables:

This is data you enter. No formulas.

"Items to Purchase" table:

You enter the item name in column A.  Note that it each must be spelled exaclty as it appears in the price list(s) or it won't find it.

You enter the quantity to purchase in column B

C2 =IF(A2="","",IFERROR(VLOOKUP(A2,Company A Price List :: A:B,2,0),"Not Available"))

The meat of this is the VLOOKUP. If an item is listed in column A it tries to find the item in Company A's price list and return the price of that item. If it can't find it the lookup creates an error which is caught by the IFERROR function and the result is "not available".

D2 =IF(A2="","",IFERROR(VLOOKUP(A2,Company B Price List :: A:B,2,0),"Not Available"))

Same idea as in C2 but is looking at Company B's price list.

E2 =IF(A2="","",MIN(C2,D2))

Picks the minimum of the two prices

F2 =IF(OR(A2="",E2=0),"",IF(\$E2=C2,C\$1,D\$1))

Finds which column has the same price as the minimum price and returns the value from the header of that column. I named columns C and D "Company A" and "Company B" for this reason. Note that Company A will be the default if the two companies have the same price for the item. The IF(OR(...) part at the beginning is for if no item was listed in the row or the product is unavailable (price in column E is 0).

G2 =IF(\$F2=G\$1,ROW(),9999)

Indicates which rows have Company A items. Again, I named the header of this column "Company A" to use it in this formula.

H2 =IF(\$F2=H\$1,ROW(),9999)

Indicates which rows have Company B items.

Fill all these formulas to the bottom of the table. You can hide the last two columns after everything is complete, but not just yet. You can hide C & D too if you don't want to see them.

"Company A Order Form" table:

The last row is a footer row. the formula in it is =SUM(D)

A2 =IFERROR(OFFSET(Items to Purchase :: A\$1,SMALL(Items to Purchase :: G,ROW()-1)-1,0),"")

B2 =IFERROR(OFFSET(Items to Purchase :: B\$1,SMALL(Items to Purchase :: G,ROW()-1)-1,0),"")

C2 =IFERROR(OFFSET(Items to Purchase :: E\$1,SMALL(Items to Purchase :: G,ROW()-1)-1,0),"")

Each of these looks for a number in column G of the "Items to Purchase" table and returns the data from that row. Trying to explain this would take some time.

D2 =IF(A2="","",C2*B2)

Fill these formulas to the bottom of the table (not including the footer row)

"Company B Order Form" table:

Once you have Company A's table done, copy it, paste it, change the table name, change all the cell references in row 2 from "Items to Purchase :: G" to "Items to Purchase :: H" then fill down with the modified formulas.

I hope this is somewhat what you were looking for. It is simple in theory but gets slightly complicated to implement, at least if you are new to spreadsheets. With more time I'm sure it could be simplified. This was my first stab at it.

• Level 1 (0 points)

This is very close to what I need but I have a few questions.

Is there any way to do something like this where the inventory list is all in front of me and I just simply check a box next to the price of the one I need to order?

Or, is there a way to have the item list as a drop down that could have all of the inventory items so I could just pick it. That would save a lot of time and alleviate the need to spell it exactly.

I need to use this form on my IPad as I walk through my business so it needs to work easily and quickly.

Thanks so so much for your input and help. This will make things so much easier and save me a lot of money.

• Level 6 (11,515 points)

Yes and that would simplify things a lot.

In the "Items to Purchase" table I had to add a second header row so "Company A' and "Company B" would span two columns each while not messing up the formulas and while still allowing the table to be sorted.

You enter all possible items in column A, the prices from each company in their respective columns (or NA if not available from that company), check one of the two boxes in the row (not both or you'll double your order), and the quantity. So, columns A-G are user data, no formulas.

H2 =IF(B3,ROW(),9999)

I2 =IF(E3,ROW(),9999)

Fill down with these formulas.

In the "Order Form" table I came up with better formulas, using INDEX instead of OFFSET,

A2 =IFERROR(INDEX(Items to Purchase :: A,SMALL(Items to Purchase :: H,ROW()-1)),"")

B2 =IFERROR(INDEX(Items to Purchase :: C,SMALL(Items to Purchase :: H,ROW()-1)),"")

C2 =IFERROR(INDEX(Items to Purchase :: G,SMALL(Items to Purchase :: H,ROW()-1)),"")

D2 =IF(OR(A2="",B2="NA"),"",B2*C2)

Fill down with these formulas.

Copy/Paste the table and make the necessary changes like before to create Company B's table

If you didn't think of it already, you can copy/paste the formulas from here into your document to make it easier. Use the same table names. You can change the names later if you want to.

Also, Sheet 1 in the screenshot above isn't necessary. It was the one I did last night.

• Level 1 (0 points)

This looks great, the only thing I can't figure out... I would like to create a new sheet for each Company Order Form(Midwest Items). What do I need to change in these formulas and what fields do Ilink back to in sheet 1 (Inventory)?

A2 =IFERROR(INDEX(Items to Purchase :: A,SMALL(Items to Purchase :: H,ROW()-1)),"")

B2 =IFERROR(INDEX(Items to Purchase :: C,SMALL(Items to Purchase :: H,ROW()-1)),"")

C2 =IFERROR(INDEX(Items to Purchase :: G,SMALL(Items to Purchase :: H,ROW()-1)),"")

D2 =IF(OR(A2="",B2="NA"),"",B2*C2)

• Level 1 (0 points)

I figured it out... thanks so much.