Sum Problem

  • Thread starter Thread starter alanled
  • Start date Start date
A

alanled

This is the second part to a problem Ive descussed in a previous threa
(thanks Karen). I have 2 rows one with part numbers in and one with th
total amount of those parts in stores. Two separate cells have bee
dedicated one to enter a requested part number and the other to ente
the amount taken out of stores. As it stands this works, but th
problem I have is when the amount in the request box is cleared th
total goes back up to its original which is no good. Is there a way
can prevent the total from going back up to its original so that ever
time i remove a part the total goes down, and just one final question
can I put some kind of low level warning when the quantities are dow
to a set amount?? many thanks for any help
 
alanled said:
This is the second part to a problem Ive descussed in a previous thread
(thanks Karen). I have 2 rows one with part numbers in and one with the
total amount of those parts in stores. Two separate cells have been
dedicated one to enter a requested part number and the other to enter
the amount taken out of stores. As it stands this works, but the
problem I have is when the amount in the request box is cleared the
total goes back up to its original which is no good. Is there a way I
can prevent the total from going back up to its original so that every
time i remove a part the total goes down, and just one final question,
can I put some kind of low level warning when the quantities are down
to a set amount?? many thanks for any help.
Click to expand...
I read the earlier thread - but i can see that you want the amount
removed permanently. Although this can be easily achieved with but a
few lines of VBA code, relying solely on formulas requires you to keep
track of every transaction you make.
You could clear all the transactions once a week or month.
After copying the values from the calculated column into the quantity
column (using paste special - and pasting values only)

As for the low level warning - conditional formatting can take care of
that - although I would probably recommend you add a third column
containing the low level for each Part Number. I'm only guessing but i
bet you have different low levels for different parts.

Here would be just 1 possible solution using formulas.

Columns H & I would contain the list of parts taken (say up to 100 rows)
ColumnH ColumnI
Part QtyTaken
==== ========
Part01 5
Part05 23
Part01 3
Part02 7
Part01 12
You just add new items to the bottom of this list
And yes you can duplicate the same part number

ColumnA ColumnB ColumnC ColumnD
PartNo Qty LowLvl Remaining
====== === ====== =========
Part01 20 10 =B2-SUMIF(H$2:H$101,A2,I$2:I$101)
Part02 98 20 Conditional Format the above before copying
Part03 54 10
etc.

Use Format > ConditionalFormatting on cell D2 (the one with the formula)
Condition 1
[Cell Value is] [Less Than or Equal to] [=$C2]
And change the format to what you want eg. Red Font, Bold etc.

Then copy that format and Formula down.

Hope it helps
George
 
Thanks George, can i have two cells at the top of the page which the
send the contents to generate a list in H and I. Im just trying to mak
it user friendly so you dont have to track down a long list.
*_STORES_CONTROL_SHEET_____*

*ITEM REQUIRED=* 1/4 X 3.4 *QTY* 24

NO. ITEM TOTAL IN STORES LOW LEVEL REMAINING
1 1/4 X 12L MSC 100.00 FALSE
2 1/4 X 3.4 150 76
3
4
5
6
 
alanled said:
Thanks George, can i have two cells at the top of the page which then
send the contents to generate a list in H and I. Im just trying to make
it user friendly so you dont have to track down a long list.
*_STORES_CONTROL_SHEET_____*

*ITEM REQUIRED=* 1/4 X 3.4 *QTY* 24

NO. ITEM TOTAL IN STORES LOW LEVEL REMAINING
1 1/4 X 12L MSC 100.00 FALSE
2 1/4 X 3.4 150 76
3
4
5
6
7
Click to expand...
Unfortunately not without some human or VBA intervention.
As there is no formula that can permanently modify a cell.

With the method I described you would have to enter your data down the
list (in columns H and I)

It is possible to view the last item you added at the top of the sheet.
But the actual data entry needs to be done in the columns themselves.


However...
What you require can be easily achieved with a few lines of VBA code
attached to a Command Button (to commit the deduction) and permanently
adjust the values in your sheet.
But I'm not sure VBA is appropriate for your level of expertise?


George
 
Back
Top