Simple Stock Control Sheet

Im Trying To Write A Simple Sheet That Basicly Lists All The Components In Stock With There Quantities, And Then Have A Cell Dedicated To Selecting A Component And Entering A Quantity That Has Been Removed From The Total Individual Amount.i.e

B1=(item)msc213 C1=(qty)23
G1=(item Selector, Select Say)msc213 H1=(qty Removed From Stores)2
That Would Then Subtract The Amount From The Right Cell 2-23=21.

As Im A Beginnner Any Help With The Formula Would Be Great!!!
Many Thanks

Hi Alan,

I use Excel 2003.

I'm not sure if I'm truly clear on what you're seeking to do with your
spreadsheet, but here's my understanding (for what it's worth! LOL).

I'm guessing that you'd like to be able to enter a code such as
"msc213" into a cell such as G2, and a number like "2" into a cell such
as H2, which would always deduct the stock sold from the appropriate
category of each of the identified inventories automatically.

I'm not an Excel whiz by any means, but if my understanding is correct
about what you'd like to do, here's how I might approach that challenge
in Excel 2003 (although my method may not be practical and I'm sure
there are a lot of experts here who can help you in a much better way).
There may even be some software programs that can work around this
problem in a much simpler way.

Let's say you've got all your inventory items described in column
B...using 3 different names: "msc213" (B3), "xyz214"(B4) and
"nnn204"(B5), and they're listed down column B in those rows. (I put a
blank row B2 between the title in B1 and the first data input in row
B3).

In your next column "C" you've got the original quantities for each.
Again C3, C4 and C5 that correspond with those rows in the B column.

Let's say you want to create a column "D" which represents the "final"
total in each category of inventory...again...D3, D4, D5. (All the rows
correspond with one another).

I'm understanding that you may want to be able to update the totals in
each of these cells in column "D" just by typing a simple name into
cell "G2" and a number into cell "H2" which would make a deduction to
the corresponding total in the "D" (total) column of each category.

In this case, I think that cells "G2" and H2" would need to be
constants (identified by a $ sign), and that each of your rows of
inventory could only reflect the latest changes you made to them.
(Again, remember that I am just a novice myself, and really can't
guarantee this would work...but heck, it may be worth a try, right?)


Okay so here's what forumula I would place in cell D3 for example:

=IF($G2="msc213",C3-$H2,IF($G2="xyz214",D3,IF($G2="nnn204",D3)))

In cell D4:

=IF($G2="xyz214",C4-$H2,IF($G2="msc213",D4,IF($G2="nnn204",D4)))

In cell D5:

=IF($G2="nnn204",C5-$H2,IF($G2="msc213",D5,IF($G2="xyz214",D5,IF($G2="nnn204",D5))))

Basically I guess what I was thinking is that if you want to insert an
indentifying name into cell G2 and a quantity into cell H2 and have it
reflect the changes to different rows at any given time, for each of
the rows, you need to let that particular cell "D" know the difference
between leaving it alone or updating it.

I'm sure this is a dinosaur's way of doing things, but not being an
Excel expert, I can't think of any other workaround!

Hopefully someone will be along soon to resolve your issue in a much
easier way.

Best regards and good luck!

Karen

Hi Alan,

I use Excel 2003.

I'm not sure if I'm truly clear on what you're seeking to do with your
spreadsheet, but here's my understanding (for what it's worth! LOL).

I'm guessing that you'd like to be able to enter a code such as
"msc213" into a cell such as G2, and a number like "2" into a cell such
as H2, which would always deduct the stock sold from the appropriate
category of each of the identified inventories automatically.

I'm not an Excel whiz by any means, but if my understanding is correct
about what you'd like to do, here's how I might approach that challenge
in Excel 2003 (although my method may not be practical and I'm sure
there are a lot of experts here who can help you in a much better way).
There may even be some software programs that can work around this
problem in a much simpler way.

Let's say you've got all your inventory items described in column
B...using 3 different names: "msc213" (B3), "xyz214"(B4) and
"nnn204"(B5), and they're listed down column B in those rows. (I put a
blank row B2 between the title in B1 and the first data input in row
B3).

In your next column "C" you've got the original quantities for each.
Again C3, C4 and C5 that correspond with those rows in the B column.

Let's say you want to create a column "D" which represents the "final"
total in each category of inventory...again...D3, D4, D5. (All the rows
correspond with one another).

I'm understanding that you may want to be able to update the totals in
each of these cells in column "D" just by typing a simple name into
cell "G2" and a number into cell "H2" which would make a deduction to
the corresponding total in the "D" (total) column of each category.

In this case, I think that cells "G2" and H2" would need to be
constants (identified by a $ sign), and that each of your rows of
inventory could only reflect the latest changes you made to them.
(Again, remember that I am just a novice myself, and really can't
guarantee this would work...but heck, it may be worth a try, right?)


Okay so here's what forumula I would place in cell D3 for example:

=IF($G2="msc213",C3-$H2,IF($G2="xyz214",D3,IF($G2="nnn204",D3)))

In cell D4:

=IF($G2="xyz214",C4-$H2,IF($G2="msc213",D4,IF($G2="nnn204",D4)))

In cell D5:

=IF($G2="nnn204",C5-$H2,IF($G2="msc213",D5,IF($G2="xyz214",D5,IF($G2="nnn204",D5))))

Basically I guess what I was thinking is that if you want to insert an
indentifying name into cell G2 and a quantity into cell H2 and have it
reflect the changes to different rows at any given time, for each of
the rows, you need to let that particular cell "D" know the difference
between leaving it alone or updating it.

I'm sure this is a dinosaur's way of doing things, but not being an
Excel expert, I can't think of any other workaround!

Hopefully someone will be along soon to resolve your issue in a much
easier way.

Best regards and good luck!

Karen