software

I've tried to use Vlookup for this but was unable to get it working.
Here's what I need:

In column 3 I have:

dog
dog1
cat
cat2

I'm trying to get an inventory management tool by looking at column 3 and
subtracting 1 from the previous row, so I would have:

Column 3 Column 4 for dog Column 5 for dog1...
100
dog 99
dog1 0
cat 0
cat2 0
dog 98

So, if I have quot;dogquot; in row 3 then I get a formula to look up quot;dogquot; then
subtract 1 from the previous row (or previous valid number, maybe not 0) and
give me the result, and if it is not quot;dogquot; it'd show a 0.

Hope I was clear enough... PLEASE HELP!

I am assuming 100 is in D2 and 99 is in D3. Then the formula for D3 and
down is:

=$D$2-COUNTIF($C$3:C3, quot;dogquot;)

Alternatively, if you have the words dog, dog1, cat, cat1 etc in D1, E1
etc, you can use the following formula in D3 and copy accross and down:

=$D$2-COUNTIF($C$3:C3, D$1)

HTH
Kostis VezeridesIf having a zero (0) was not important, you could set it up like this:

CriteriaFormula Result
100 100
dog=IF(A3=quot;Dogquot;,B2-1,0) 99
Dog1=IF(A4=quot;Dogquot;,MIN($B$2:B3)-1,quot;0quot;)0
Cat=IF(A5=quot;Dogquot;,MIN($B$2:B4)-1,quot;0quot;)0
Cat2=IF(A6=quot;Dogquot;,MIN($B$2:B5)-1,quot;0quot;)0
dog=IF(A7=quot;Dogquot;,MIN($B$2:B6)-1,quot;0quot;)98quot;DMquot; wrote:

gt; I've tried to use Vlookup for this but was unable to get it working.
gt; Here's what I need:
gt;
gt; In column 3 I have:
gt;
gt; dog
gt; dog1
gt; cat
gt; cat2
gt;
gt; I'm trying to get an inventory management tool by looking at column 3 and
gt; subtracting 1 from the previous row, so I would have:
gt;
gt; Column 3 Column 4 for dog Column 5 for dog1...
gt; 100
gt; dog 99
gt; dog1 0
gt; cat 0
gt; cat2 0
gt; dog 98
gt;
gt; So, if I have quot;dogquot; in row 3 then I get a formula to look up quot;dogquot; then
gt; subtract 1 from the previous row (or previous valid number, maybe not 0) and
gt; give me the result, and if it is not quot;dogquot; it'd show a 0.
gt;
gt; Hope I was clear enough... PLEASE HELP!

Is it possible to add a second variable to the formula to also look for
quot;dog1quot; at the same time? How do I do that? Something like
=$D$2-COUNTIF($C$3:C3, quot;dogquot;; quot;dog1quot;)

Thank you!

quot;vezeridquot; wrote:

gt; I am assuming 100 is in D2 and 99 is in D3. Then the formula for D3 and
gt; down is:
gt;
gt; =$D$2-COUNTIF($C$3:C3, quot;dogquot;)
gt;
gt; Alternatively, if you have the words dog, dog1, cat, cat1 etc in D1, E1
gt; etc, you can use the following formula in D3 and copy accross and down:
gt;
gt; =$D$2-COUNTIF($C$3:C3, D$1)
gt;
gt; HTH
gt; Kostis Vezerides
gt;
gt;

If your list only has quot;Dogquot; or quot;Dog1quot; then try:

=IF(OR(D3=quot;Dogquot;,D3=quot;Dog1quot;),($F$2-COUNTIF($D$33,quot;dog*quot;)),0)

Otherwise is you have more types of quot;Dogquot; then use:

=IF(OR(A3=quot;Dogquot;,A3=quot;Dog1quot;),($B$2-(COUNTIF($A$3:A3,quot;dogquot;) COUNTIF($A$3:A3,quot;dog1quot;))), 0)

quot;DMquot; wrote:

gt; Is it possible to add a second variable to the formula to also look for
gt; quot;dog1quot; at the same time? How do I do that? Something like
gt; =$D$2-COUNTIF($C$3:C3, quot;dogquot;; quot;dog1quot;)
gt;
gt; Thank you!
gt;
gt; quot;vezeridquot; wrote:
gt;
gt; gt; I am assuming 100 is in D2 and 99 is in D3. Then the formula for D3 and
gt; gt; down is:
gt; gt;
gt; gt; =$D$2-COUNTIF($C$3:C3, quot;dogquot;)
gt; gt;
gt; gt; Alternatively, if you have the words dog, dog1, cat, cat1 etc in D1, E1
gt; gt; etc, you can use the following formula in D3 and copy accross and down:
gt; gt;
gt; gt; =$D$2-COUNTIF($C$3:C3, D$1)
gt; gt;
gt; gt; HTH
gt; gt; Kostis Vezerides
gt; gt;
gt; gt;