shipment tracking

[mdarsot]

How do you design a spreadsheet that will reflect what is sent and what has come back in. An entry will reflect sent but how do you reflect as in when it comes back.

What is the best method.
1) Currently i am using and sumif formula and putting x behind entire row and then counting that as in for my summary sheet. However this idea dont work if you are tracking 10 things seperately on 1 row.
ie

Ship Date come-in product-1 Product-2
Mar-14-2008 X 125 333

Currently i have something like above designed with SUMIF where it goes and see if there is X in come-in column then sumif product-1 & product-2 as SHIPMENT RECIVED.

This works if all products are recived at the same time. however in our business that is not the case. and to individually assing come-in field for each set of record is not a good design.

Please suggest a better way to track each product when they are recived.




2) Would sumif work on font formatting. meaning let say when the shipment comes back. i change the font to strike through. this triggers the summary sheet formula saying the shipment is in. not outstanding.

3) Is there another better method.

 

[AnotherHiggins]

It sounds like you're trying to use Excel as a database.

Do you access to MS Access or another database program?

->However this idea dont work if you are tracking 10 things seperately on 1 row

That's a great example of why you don't track 10 things on one row. In order to be able to use all of Excel's many powerful analysis and reporting tools, you should store your data in a normalized table. Something like this:
[tt]
Ship Date come-in product Count
Mar-14-2008 X 1 125
Mar-14-2008 X 2 333[/tt]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.