Using a dropdown list to check off if inventory arrived (subtract from total)

[leo255]

Hi everyone!

I have a very basic spreadsheet I'm working on. I have a row for each sku, and then have the following columns: Qty to Order, Invoice #1, Date, Qty to Order, Invoice #2, Date, Total, Items in transit, and then I have some columns with dropdown lists, entitled shipment arrival #1, #2, where the user can select "Yes" or "No".

I'm totaling the qty's in total, and then using the following formula for Items in transit: =IF(AND(K4="Yes", L4 = "Yes"), I4 - C4-F4, IF(K4 = "Yes", I4-C4, "")). This is checking first if the two boxes are checked "Yes" and then if the first box is checked "Yes," and making the appropriate subtractions from the total. As you can see, this works, but isn't that practical (It's also not accounting for when the 2nd box is the only one checked).

I need to add two more sets of columns of "Qty to Order, Invoice, and Date," as well as two more columns of shipment arrival for the user to check "Yes" or "No", and having four of these, this kind of formula will not be practical. By my math, there will be 24 different permutations/possibilities (for you math guys, 4! = 4*3*2*1). I'm wondering if there is any easier way to do this, and/or if there are functions that I could use that would be better?

Thanks.

[humdingaling]

can you attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[leo255]

Thanks for the reply! Here it is:

https://onedrive.live.com/redir?resi...ECA9DD83%21122

[leo255]

Just thinking out loud, but would a sumif() function work here? The criteria would obviously just be "Yes," as a "No" is the same thing as being blank. Maybe I would have to put the shipment arrival dropdown list right next to the qty ordered, invoice, date, etc.

[humdingaling]

you are over thinking it

in J4 put

Please Login or Register  to view this content.

in I4 put

Please Login or Register  to view this content.

fill down

I column now is sum of all orders minus what has arrived
ie column J

J is now
if column 1 is yes put value of order 1 otherwise 0 (not arrived)+if column 2 is yes put value of order 2 otherwise 0 (not arrived)
if it is blank for either it is also 0 (not arrived)