Previous Topic Next Topic

Inventory set-up

Closed
simeon - Mar 10, 2015 at 11:30 AM
 Simeon - Mar 15, 2015 at 04:31 PM
Dear all,

I have been struggling with setting up my inventory process in excel.

So I have a live stock with my total actual unprinted t-shirts. The following sheet, converts the unprinted t-shirts to printed t-shirts. Each time I convert one or more t-shirts I want to deduct them from my live stock of unprinted t-shirt. Does this sound logic?

Row B describing the sizes, row C the total converted units and E2 the live stock;

=IF ROW B has the (letter) S value, the C2 value (or equivalent cell) is deducted from E2

As you can tell, my experience with more complicated excel formulas is limited. The troubleshooters are checking a complete row for a certain value + a cell being a letter instead of a number.

I really hope you can help.

Thank you in advance

Related:

1 response

Answer 1 / 1
cmarzahn Posts 35 Registration date Wednesday February 18, 2015 Status Member Last seen March 13, 2015 7
Mar 11, 2015 at 04:48 PM
Hello,

I think I understand. S in the B column means sold and you want to deduct the sold inventory in C from the in stock inventory in E. Could you put a column in F that looks for an S in B and then takes C from E putting the result in F? If we were talking about row 5 the formula placed in F might look like this. (And you could use F for the current in stock count.)

=IF(OR(E5=0,E5=""),"",IF(B5="S",E5-C5,E7))

Here's a link to a file sample in DropBox.

https://www.dropbox.com/s/7wlv2wr64vhdg8n/Inventory.xlsx?dl=0

Hope it helps.
Simeon
Mar 15, 2015 at 04:31 PM
Thank you for your feedback.

Sorry for the late reply; I have been trying to puzzle with your formula. I think you came very close, let me try to explain myself in more logical terms?

The letter S, M, L, XL is relevant to the different sizes, my quest is to have a formula that filters Row B for the size S or other size. When entered S in B, it should deduct the total converted units of F from the total blank stock in I (size S). This way I can monitor the actual blank stock while printing them with different print without running out of (blank) stock.

See: https://www.dropbox.com/sh/658x2j8bsgceg98/AABKDAs9GzlwFY0wYhBivm5ja?dl=0

What do you think?

tx again.