multiple line two accumulators

[EED]

I'm able to get a two cell accumlator going but my problem is I have multiple
lines that I need this for.

Ex: A1(amt entered) F2(accum. total), which goes from A1 & F2 thru A500
& F500

These totals are for each line/part number for whats being shipped out, with
keeping overal total for what has been shipped for that part.

I've been looking at different codes but havent found the information needed
to make this happen. Hopefully it can but please advise with any assistance.
Thanks!!!!

[K Dales]

If I understand correctly, you want the value in column F to indicate the
accumulated total (running total) of the amounts in column A for a particular
part number (which is in another, unspecified column? I will have to use that
column, so I will arbitrarily make it column C). You could use a worksheet
formula such as this in your F column - it is written for F2; could be copied
down in column F to work for the rest of the range - but only if you follow
the relative/absolute references carefully:
F2 = SUMIF($C$1:$C2,$C2,$A$1$A2)

"EED" wrote:

> I'm able to get a two cell accumlator going but my problem is I have multiple
> lines that I need this for.
>
> Ex: A1(amt entered) F2(accum. total), which goes from A1 & F2 thru A500
> & F500
>
> These totals are for each line/part number for whats being shipped out, with
> keeping overal total for what has been shipped for that part.
>
> I've been looking at different codes but havent found the information needed
> to make this happen. Hopefully it can but please advise with any assistance.
> Thanks!!!!
>

[EED]

Let me rephrase....

This is for incoming parts that we're dealing with. No duplicate part
numbers either and its too keep track of total amt of parts shipped for THAT
line. Which there could be up to 500 diff lines but needs only keep
accumulative total per part/line.

I can get an accumulator working for one line but I need this to work for
all lines if need be. Ultimately the amt entered in E1 should accumulate to
F1 - as E2/D2, E3/D3. Again this is only for each line/part number, not an
overall total for certain parts or all parts. I hope this is more clear on
what I need.

the spreadsheet goes....
A B C D
E F
Date - Part number - Job number - Quantity - Quantity Shipped - Total
Shipped

today
1 XXX XXXX XXXX 5000 2000
3000
2 XXX XXXX XXXX 2500 1500
2000
3
4

Thanks!

"K Dales" wrote:

> If I understand correctly, you want the value in column F to indicate the
> accumulated total (running total) of the amounts in column A for a particular
> part number (which is in another, unspecified column? I will have to use that
> column, so I will arbitrarily make it column C). You could use a worksheet
> formula such as this in your F column - it is written for F2; could be copied
> down in column F to work for the rest of the range - but only if you follow
> the relative/absolute references carefully:
> F2 = SUMIF($C$1:$C2,$C2,$A$1$A2)
>
> "EED" wrote:
>
> > I'm able to get a two cell accumlator going but my problem is I have multiple
> > lines that I need this for.
> >
> > Ex: A1(amt entered) F2(accum. total), which goes from A1 & F2 thru A500
> > & F500
> >
> > These totals are for each line/part number for whats being shipped out, with
> > keeping overal total for what has been shipped for that part.
> >
> > I've been looking at different codes but havent found the information needed
> > to make this happen. Hopefully it can but please advise with any assistance.
> > Thanks!!!!
> >

[K Dales]

I still don't quite get it: Are you trying to calculate the amount in "Total
Shipped" (column F), or something else? How are you defining "accumulated
total?" - accumulated for what? If there is no duplication in parts why is
the total not simply Quantity - Quantity shipped (e.g. F2=D2-E2)? How did
you get 2000 as the total shipped in your second line? I am either
misunderstanding the problem or the math.

"EED" wrote:

> Let me rephrase....
>
> This is for incoming parts that we're dealing with. No duplicate part
> numbers either and its too keep track of total amt of parts shipped for THAT
> line. Which there could be up to 500 diff lines but needs only keep
> accumulative total per part/line.
>
> I can get an accumulator working for one line but I need this to work for
> all lines if need be. Ultimately the amt entered in E1 should accumulate to
> F1 - as E2/D2, E3/D3. Again this is only for each line/part number, not an
> overall total for certain parts or all parts. I hope this is more clear on
> what I need.
>
> the spreadsheet goes....
> A B C D
> E F
> Date - Part number - Job number - Quantity - Quantity Shipped - Total
> Shipped
>
> today
> 1 XXX XXXX XXXX 5000 2000
> 3000
> 2 XXX XXXX XXXX 2500 1500
> 2000
> 3
> 4
>
> Thanks!
>
> "K Dales" wrote:
>
> > If I understand correctly, you want the value in column F to indicate the
> > accumulated total (running total) of the amounts in column A for a particular
> > part number (which is in another, unspecified column? I will have to use that
> > column, so I will arbitrarily make it column C). You could use a worksheet
> > formula such as this in your F column - it is written for F2; could be copied
> > down in column F to work for the rest of the range - but only if you follow
> > the relative/absolute references carefully:
> > F2 = SUMIF($C$1:$C2,$C2,$A$1$A2)
> >
> > "EED" wrote:
> >
> > > I'm able to get a two cell accumlator going but my problem is I have multiple
> > > lines that I need this for.
> > >
> > > Ex: A1(amt entered) F2(accum. total), which goes from A1 & F2 thru A500
> > > & F500
> > >
> > > These totals are for each line/part number for whats being shipped out, with
> > > keeping overal total for what has been shipped for that part.
> > >
> > > I've been looking at different codes but havent found the information needed
> > > to make this happen. Hopefully it can but please advise with any assistance.
> > > Thanks!!!!
> > >

[EED]

The totals are showing accumulative....as with the 5000 qty, 1000 were
shipped before along with the 2000 just shipped. giving a running total of
3000. nothing wrong with your math, just my way showing the example.

"K Dales" wrote:

> I still don't quite get it: Are you trying to calculate the amount in "Total
> Shipped" (column F), or something else? How are you defining "accumulated
> total?" - accumulated for what? If there is no duplication in parts why is
> the total not simply Quantity - Quantity shipped (e.g. F2=D2-E2)? How did
> you get 2000 as the total shipped in your second line? I am either
> misunderstanding the problem or the math.
>
> "EED" wrote:
>
> > Let me rephrase....
> >
> > This is for incoming parts that we're dealing with. No duplicate part
> > numbers either and its too keep track of total amt of parts shipped for THAT
> > line. Which there could be up to 500 diff lines but needs only keep
> > accumulative total per part/line.
> >
> > I can get an accumulator working for one line but I need this to work for
> > all lines if need be. Ultimately the amt entered in E1 should accumulate to
> > F1 - as E2/D2, E3/D3. Again this is only for each line/part number, not an
> > overall total for certain parts or all parts. I hope this is more clear on
> > what I need.
> >
> > the spreadsheet goes....
> > A B C D
> > E F
> > Date - Part number - Job number - Quantity - Quantity Shipped - Total
> > Shipped
> >
> > today
> > 1 XXX XXXX XXXX 5000 2000
> > 3000
> > 2 XXX XXXX XXXX 2500 1500
> > 2000
> > 3
> > 4
> >
> > Thanks!
> >
> > "K Dales" wrote:
> >
> > > If I understand correctly, you want the value in column F to indicate the
> > > accumulated total (running total) of the amounts in column A for a particular
> > > part number (which is in another, unspecified column? I will have to use that
> > > column, so I will arbitrarily make it column C). You could use a worksheet
> > > formula such as this in your F column - it is written for F2; could be copied
> > > down in column F to work for the rest of the range - but only if you follow
> > > the relative/absolute references carefully:
> > > F2 = SUMIF($C$1:$C2,$C2,$A$1$A2)
> > >
> > > "EED" wrote:
> > >
> > > > I'm able to get a two cell accumlator going but my problem is I have multiple
> > > > lines that I need this for.
> > > >
> > > > Ex: A1(amt entered) F2(accum. total), which goes from A1 & F2 thru A500
> > > > & F500
> > > >
> > > > These totals are for each line/part number for whats being shipped out, with
> > > > keeping overal total for what has been shipped for that part.
> > > >
> > > > I've been looking at different codes but havent found the information needed
> > > > to make this happen. Hopefully it can but please advise with any assistance.
> > > > Thanks!!!!
> > > >

[K Dales]

OK, so getting back to that first line of the example - how did you know that
1000 had been shipped before? That is still not clear. Basically, your
formula in F needs to be able to know where this 1000 comes from and add E
to it, but I can't give the formula yet because I need to know how you are
figuring that amount that was "shipped before."

"EED" wrote:

> The totals are showing accumulative....as with the 5000 qty, 1000 were
> shipped before along with the 2000 just shipped. giving a running total of
> 3000. nothing wrong with your math, just my way showing the example.
>
> "K Dales" wrote:
>
> > I still don't quite get it: Are you trying to calculate the amount in "Total
> > Shipped" (column F), or something else? How are you defining "accumulated
> > total?" - accumulated for what? If there is no duplication in parts why is
> > the total not simply Quantity - Quantity shipped (e.g. F2=D2-E2)? How did
> > you get 2000 as the total shipped in your second line? I am either
> > misunderstanding the problem or the math.
> >
> > "EED" wrote:
> >
> > > Let me rephrase....
> > >
> > > This is for incoming parts that we're dealing with. No duplicate part
> > > numbers either and its too keep track of total amt of parts shipped for THAT
> > > line. Which there could be up to 500 diff lines but needs only keep
> > > accumulative total per part/line.
> > >
> > > I can get an accumulator working for one line but I need this to work for
> > > all lines if need be. Ultimately the amt entered in E1 should accumulate to
> > > F1 - as E2/D2, E3/D3. Again this is only for each line/part number, not an
> > > overall total for certain parts or all parts. I hope this is more clear on
> > > what I need.
> > >
> > > the spreadsheet goes....
> > > A B C D
> > > E F
> > > Date - Part number - Job number - Quantity - Quantity Shipped - Total
> > > Shipped
> > >
> > > today
> > > 1 XXX XXXX XXXX 5000 2000
> > > 3000
> > > 2 XXX XXXX XXXX 2500 1500
> > > 2000
> > > 3
> > > 4
> > >
> > > Thanks!
> > >
> > > "K Dales" wrote:
> > >
> > > > If I understand correctly, you want the value in column F to indicate the
> > > > accumulated total (running total) of the amounts in column A for a particular
> > > > part number (which is in another, unspecified column? I will have to use that
> > > > column, so I will arbitrarily make it column C). You could use a worksheet
> > > > formula such as this in your F column - it is written for F2; could be copied
> > > > down in column F to work for the rest of the range - but only if you follow
> > > > the relative/absolute references carefully:
> > > > F2 = SUMIF($C$1:$C2,$C2,$A$1$A2)
> > > >
> > > > "EED" wrote:
> > > >
> > > > > I'm able to get a two cell accumlator going but my problem is I have multiple
> > > > > lines that I need this for.
> > > > >
> > > > > Ex: A1(amt entered) F2(accum. total), which goes from A1 & F2 thru A500
> > > > > & F500
> > > > >
> > > > > These totals are for each line/part number for whats being shipped out, with
> > > > > keeping overal total for what has been shipped for that part.
> > > > >
> > > > > I've been looking at different codes but havent found the information needed
> > > > > to make this happen. Hopefully it can but please advise with any assistance.
> > > > > Thanks!!!!
> > > > >

[EED]

It was an example as though 1000 had already been shipped out....then adding
2000 more to F, gave an accumulative total of 3000. With being simple, I
just need the amts put in F will be added to the amt in H - for each
indivudual line. F2 will be added to H2, F3 to H3, and so on.

"K Dales" wrote:

> OK, so getting back to that first line of the example - how did you know that
> 1000 had been shipped before? That is still not clear. Basically, your
> formula in F needs to be able to know where this 1000 comes from and add E
> to it, but I can't give the formula yet because I need to know how you are
> figuring that amount that was "shipped before."
>
> "EED" wrote:
>
> > The totals are showing accumulative....as with the 5000 qty, 1000 were
> > shipped before along with the 2000 just shipped. giving a running total of
> > 3000. nothing wrong with your math, just my way showing the example.
> >
> > "K Dales" wrote:
> >
> > > I still don't quite get it: Are you trying to calculate the amount in "Total
> > > Shipped" (column F), or something else? How are you defining "accumulated
> > > total?" - accumulated for what? If there is no duplication in parts why is
> > > the total not simply Quantity - Quantity shipped (e.g. F2=D2-E2)? How did
> > > you get 2000 as the total shipped in your second line? I am either
> > > misunderstanding the problem or the math.
> > >
> > > "EED" wrote:
> > >
> > > > Let me rephrase....
> > > >
> > > > This is for incoming parts that we're dealing with. No duplicate part
> > > > numbers either and its too keep track of total amt of parts shipped for THAT
> > > > line. Which there could be up to 500 diff lines but needs only keep
> > > > accumulative total per part/line.
> > > >
> > > > I can get an accumulator working for one line but I need this to work for
> > > > all lines if need be. Ultimately the amt entered in E1 should accumulate to
> > > > F1 - as E2/D2, E3/D3. Again this is only for each line/part number, not an
> > > > overall total for certain parts or all parts. I hope this is more clear on
> > > > what I need.
> > > >
> > > > the spreadsheet goes....
> > > > A B C D
> > > > E F
> > > > Date - Part number - Job number - Quantity - Quantity Shipped - Total
> > > > Shipped
> > > >
> > > > today
> > > > 1 XXX XXXX XXXX 5000 2000
> > > > 3000
> > > > 2 XXX XXXX XXXX 2500 1500
> > > > 2000
> > > > 3
> > > > 4
> > > >
> > > > Thanks!
> > > >
> > > > "K Dales" wrote:
> > > >
> > > > > If I understand correctly, you want the value in column F to indicate the
> > > > > accumulated total (running total) of the amounts in column A for a particular
> > > > > part number (which is in another, unspecified column? I will have to use that
> > > > > column, so I will arbitrarily make it column C). You could use a worksheet
> > > > > formula such as this in your F column - it is written for F2; could be copied
> > > > > down in column F to work for the rest of the range - but only if you follow
> > > > > the relative/absolute references carefully:
> > > > > F2 = SUMIF($C$1:$C2,$C2,$A$1$A2)
> > > > >
> > > > > "EED" wrote:
> > > > >
> > > > > > I'm able to get a two cell accumlator going but my problem is I have multiple
> > > > > > lines that I need this for.
> > > > > >
> > > > > > Ex: A1(amt entered) F2(accum. total), which goes from A1 & F2 thru A500
> > > > > > & F500
> > > > > >
> > > > > > These totals are for each line/part number for whats being shipped out, with
> > > > > > keeping overal total for what has been shipped for that part.
> > > > > >
> > > > > > I've been looking at different codes but havent found the information needed
> > > > > > to make this happen. Hopefully it can but please advise with any assistance.
> > > > > > Thanks!!!!
> > > > > >

[K Dales]

Then the formula would be simple: =F2+H2

"EED" wrote:

> It was an example as though 1000 had already been shipped out....then adding
> 2000 more to F, gave an accumulative total of 3000. With being simple, I
> just need the amts put in F will be added to the amt in H - for each
> indivudual line. F2 will be added to H2, F3 to H3, and so on.
>
> "K Dales" wrote:
>
> > OK, so getting back to that first line of the example - how did you know that
> > 1000 had been shipped before? That is still not clear. Basically, your
> > formula in F needs to be able to know where this 1000 comes from and add E
> > to it, but I can't give the formula yet because I need to know how you are
> > figuring that amount that was "shipped before."
> >
> > "EED" wrote:
> >
> > > The totals are showing accumulative....as with the 5000 qty, 1000 were
> > > shipped before along with the 2000 just shipped. giving a running total of
> > > 3000. nothing wrong with your math, just my way showing the example.
> > >
> > > "K Dales" wrote:
> > >
> > > > I still don't quite get it: Are you trying to calculate the amount in "Total
> > > > Shipped" (column F), or something else? How are you defining "accumulated
> > > > total?" - accumulated for what? If there is no duplication in parts why is
> > > > the total not simply Quantity - Quantity shipped (e.g. F2=D2-E2)? How did
> > > > you get 2000 as the total shipped in your second line? I am either
> > > > misunderstanding the problem or the math.
> > > >
> > > > "EED" wrote:
> > > >
> > > > > Let me rephrase....
> > > > >
> > > > > This is for incoming parts that we're dealing with. No duplicate part
> > > > > numbers either and its too keep track of total amt of parts shipped for THAT
> > > > > line. Which there could be up to 500 diff lines but needs only keep
> > > > > accumulative total per part/line.
> > > > >
> > > > > I can get an accumulator working for one line but I need this to work for
> > > > > all lines if need be. Ultimately the amt entered in E1 should accumulate to
> > > > > F1 - as E2/D2, E3/D3. Again this is only for each line/part number, not an
> > > > > overall total for certain parts or all parts. I hope this is more clear on
> > > > > what I need.
> > > > >
> > > > > the spreadsheet goes....
> > > > > A B C D
> > > > > E F
> > > > > Date - Part number - Job number - Quantity - Quantity Shipped - Total
> > > > > Shipped
> > > > >
> > > > > today
> > > > > 1 XXX XXXX XXXX 5000 2000
> > > > > 3000
> > > > > 2 XXX XXXX XXXX 2500 1500
> > > > > 2000
> > > > > 3
> > > > > 4
> > > > >
> > > > > Thanks!
> > > > >
> > > > > "K Dales" wrote:
> > > > >
> > > > > > If I understand correctly, you want the value in column F to indicate the
> > > > > > accumulated total (running total) of the amounts in column A for a particular
> > > > > > part number (which is in another, unspecified column? I will have to use that
> > > > > > column, so I will arbitrarily make it column C). You could use a worksheet
> > > > > > formula such as this in your F column - it is written for F2; could be copied
> > > > > > down in column F to work for the rest of the range - but only if you follow
> > > > > > the relative/absolute references carefully:
> > > > > > F2 = SUMIF($C$1:$C2,$C2,$A$1$A2)
> > > > > >
> > > > > > "EED" wrote:
> > > > > >
> > > > > > > I'm able to get a two cell accumlator going but my problem is I have multiple
> > > > > > > lines that I need this for.
> > > > > > >
> > > > > > > Ex: A1(amt entered) F2(accum. total), which goes from A1 & F2 thru A500
> > > > > > > & F500
> > > > > > >
> > > > > > > These totals are for each line/part number for whats being shipped out, with
> > > > > > > keeping overal total for what has been shipped for that part.
> > > > > > >
> > > > > > > I've been looking at different codes but havent found the information needed
> > > > > > > to make this happen. Hopefully it can but please advise with any assistance.
> > > > > > > Thanks!!!!
> > > > > > >

[EED]

I'll try one more time and be simple with it...

for each row...in keeping track of what has been shipped out.

I need to enter the 'shipped amt' - then have it keep a running total in
'total shipped' - for that specific line. I have multiple lines/diff part
numbers. Each line has to keep a running total of total shipped for that
part/row.

If I only needed to keep a running total on one line, I can do that w/a
normal accumulator code. But my problem is having this be performed for many
lines....and again, each row/total has no affect on other totals. I need the
running total for each line/part.

[K Dales]

Here is the source of my confusion:
"I need to enter the 'shipped amt' - then have it keep a running total in
'total shipped' - for that specific line." And from an earlier post: "No
duplicate part
numbers either and its too keep track of total amt of parts shipped for THAT
line. Which there could be up to 500 diff lines but needs only keep
accumulative total per part/line."

"If I only needed to keep a running total on one line, I can do that w/a
normal accumulator code. But my problem is having this be performed for many
lines...."

I realize I have badly misinterpreted your question, and still am, but I am
trying to get past the apparent inconsistency in the above. If the formula
only acts on the same values for every line (F + H) in the range you gave
(A1:F500) then do the formula as I stated and simply copy it from cell F2
down through F500. But if you need to do a running total that does rely on
the value from another line, or set of lines, I need that clarified to figure
out how the running total is to be computed.

I hope I am not appearing sarcastic - I do not mean to be and I am doing my
best to help you. Communicating about spreadsheet structure via text
messages can often be difficult and I am sure if I just was there as you
explained and showed it to me I would get it.

"EED" wrote:

> I'll try one more time and be simple with it...
>
> for each row...in keeping track of what has been shipped out.
>
> I need to enter the 'shipped amt' - then have it keep a running total in
> 'total shipped' - for that specific line. I have multiple lines/diff part
> numbers. Each line has to keep a running total of total shipped for that
> part/row.
>
> If I only needed to keep a running total on one line, I can do that w/a
> normal accumulator code. But my problem is having this be performed for many
> lines....and again, each row/total has no affect on other totals. I need the
> running total for each line/part.

[EED]

Running totals will be for each individual line/per part number. These are
all seperate/diff part numbers. So the accumulated total is for total
shipped, in which you will input a daily shipped figure per part. Then that
daily number will be added into the 'total shipped' - again, this is for each
row/part number. I could do this easily if I wanted a circular reference but
not w/the amt of lines I'm working with. I've setup a 2 cell accumulator but
only can get it working for one line, not all the other lines that its needed
on also.

Hope this helps and thanks!

"K Dales" wrote:

> Here is the source of my confusion:
> "I need to enter the 'shipped amt' - then have it keep a running total in
> 'total shipped' - for that specific line." And from an earlier post: "No
> duplicate part
> numbers either and its too keep track of total amt of parts shipped for THAT
> line. Which there could be up to 500 diff lines but needs only keep
> accumulative total per part/line."
>
> "If I only needed to keep a running total on one line, I can do that w/a
> normal accumulator code. But my problem is having this be performed for many
> lines...."
>
> I realize I have badly misinterpreted your question, and still am, but I am
> trying to get past the apparent inconsistency in the above. If the formula
> only acts on the same values for every line (F + H) in the range you gave
> (A1:F500) then do the formula as I stated and simply copy it from cell F2
> down through F500. But if you need to do a running total that does rely on
> the value from another line, or set of lines, I need that clarified to figure
> out how the running total is to be computed.
>
> I hope I am not appearing sarcastic - I do not mean to be and I am doing my
> best to help you. Communicating about spreadsheet structure via text
> messages can often be difficult and I am sure if I just was there as you
> explained and showed it to me I would get it.
>
> "EED" wrote:
>
> > I'll try one more time and be simple with it...
> >
> > for each row...in keeping track of what has been shipped out.
> >
> > I need to enter the 'shipped amt' - then have it keep a running total in
> > 'total shipped' - for that specific line. I have multiple lines/diff part
> > numbers. Each line has to keep a running total of total shipped for that
> > part/row.
> >
> > If I only needed to keep a running total on one line, I can do that w/a
> > normal accumulator code. But my problem is having this be performed for many
> > lines....and again, each row/total has no affect on other totals. I need the
> > running total for each line/part.

[K Dales]

OK, I think I am starting to see and where my misconception was. I was
envisioning a system where every separate order shipped, or whatever, was on
a separate line and you had to develop a running total from this, or else
that each line was a single entry that was entered once and not changed.
But that apparently is not correct. I may still be missing a bit, so let's
see if I understand: you are not adding anything from another line, but
(using the existing line) you are entering a new "Quantity Shipped"
(replacing the existing value in that cell) and you want to take that number
and add it to whatever was the existing total from that line? And yes, that
would be a circular reference if you handled it "normally" through any
standard Excel function.

I have a thought on how to handle that scenario, if I have it correct, but
am just finishing up my lunch break here and need to get back to work! If
you read this in the meantime, perhaps you can verify if I have the right
concept now of what you need to do - and also, if you could let me know what
your VBA/macro experience level is it would help me know how detailed to make
the instructions!

"EED" wrote:

> Running totals will be for each individual line/per part number. These are
> all seperate/diff part numbers. So the accumulated total is for total
> shipped, in which you will input a daily shipped figure per part. Then that
> daily number will be added into the 'total shipped' - again, this is for each
> row/part number. I could do this easily if I wanted a circular reference but
> not w/the amt of lines I'm working with. I've setup a 2 cell accumulator but
> only can get it working for one line, not all the other lines that its needed
> on also.
>
> Hope this helps and thanks!
>
> "K Dales" wrote:
>
> > Here is the source of my confusion:
> > "I need to enter the 'shipped amt' - then have it keep a running total in
> > 'total shipped' - for that specific line." And from an earlier post: "No
> > duplicate part
> > numbers either and its too keep track of total amt of parts shipped for THAT
> > line. Which there could be up to 500 diff lines but needs only keep
> > accumulative total per part/line."
> >
> > "If I only needed to keep a running total on one line, I can do that w/a
> > normal accumulator code. But my problem is having this be performed for many
> > lines...."
> >
> > I realize I have badly misinterpreted your question, and still am, but I am
> > trying to get past the apparent inconsistency in the above. If the formula
> > only acts on the same values for every line (F + H) in the range you gave
> > (A1:F500) then do the formula as I stated and simply copy it from cell F2
> > down through F500. But if you need to do a running total that does rely on
> > the value from another line, or set of lines, I need that clarified to figure
> > out how the running total is to be computed.
> >
> > I hope I am not appearing sarcastic - I do not mean to be and I am doing my
> > best to help you. Communicating about spreadsheet structure via text
> > messages can often be difficult and I am sure if I just was there as you
> > explained and showed it to me I would get it.
> >
> > "EED" wrote:
> >
> > > I'll try one more time and be simple with it...
> > >
> > > for each row...in keeping track of what has been shipped out.
> > >
> > > I need to enter the 'shipped amt' - then have it keep a running total in
> > > 'total shipped' - for that specific line. I have multiple lines/diff part
> > > numbers. Each line has to keep a running total of total shipped for that
> > > part/row.
> > >
> > > If I only needed to keep a running total on one line, I can do that w/a
> > > normal accumulator code. But my problem is having this be performed for many
> > > lines....and again, each row/total has no affect on other totals. I need the
> > > running total for each line/part.

[EED]

No, I'm not adding anything from other lines. Each line will be different
but need to keep a running total for each row/parts shipped. I'd prefer to
enter in the 'shipped amt for the day' and that amt be added into 'total
shipped' automatically. Each are individual lines, no other lines affect
each other. What I enter in for amt shipped on row 1 has nothing to do with
the amt shipped on row 2-500. Each row has a diff part and qty...but need to
keep a running total for each row/part #.

Hope this clears the fog and thanks again!

"K Dales" wrote:

> OK, I think I am starting to see and where my misconception was. I was
> envisioning a system where every separate order shipped, or whatever, was on
> a separate line and you had to develop a running total from this, or else
> that each line was a single entry that was entered once and not changed.
> But that apparently is not correct. I may still be missing a bit, so let's
> see if I understand: you are not adding anything from another line, but
> (using the existing line) you are entering a new "Quantity Shipped"
> (replacing the existing value in that cell) and you want to take that number
> and add it to whatever was the existing total from that line? And yes, that
> would be a circular reference if you handled it "normally" through any
> standard Excel function.
>
> I have a thought on how to handle that scenario, if I have it correct, but
> am just finishing up my lunch break here and need to get back to work! If
> you read this in the meantime, perhaps you can verify if I have the right
> concept now of what you need to do - and also, if you could let me know what
> your VBA/macro experience level is it would help me know how detailed to make
> the instructions!
>
> "EED" wrote:
>
> > Running totals will be for each individual line/per part number. These are
> > all seperate/diff part numbers. So the accumulated total is for total
> > shipped, in which you will input a daily shipped figure per part. Then that
> > daily number will be added into the 'total shipped' - again, this is for each
> > row/part number. I could do this easily if I wanted a circular reference but
> > not w/the amt of lines I'm working with. I've setup a 2 cell accumulator but
> > only can get it working for one line, not all the other lines that its needed
> > on also.
> >
> > Hope this helps and thanks!
> >
> > "K Dales" wrote:
> >
> > > Here is the source of my confusion:
> > > "I need to enter the 'shipped amt' - then have it keep a running total in
> > > 'total shipped' - for that specific line." And from an earlier post: "No
> > > duplicate part
> > > numbers either and its too keep track of total amt of parts shipped for THAT
> > > line. Which there could be up to 500 diff lines but needs only keep
> > > accumulative total per part/line."
> > >
> > > "If I only needed to keep a running total on one line, I can do that w/a
> > > normal accumulator code. But my problem is having this be performed for many
> > > lines...."
> > >
> > > I realize I have badly misinterpreted your question, and still am, but I am
> > > trying to get past the apparent inconsistency in the above. If the formula
> > > only acts on the same values for every line (F + H) in the range you gave
> > > (A1:F500) then do the formula as I stated and simply copy it from cell F2
> > > down through F500. But if you need to do a running total that does rely on
> > > the value from another line, or set of lines, I need that clarified to figure
> > > out how the running total is to be computed.
> > >
> > > I hope I am not appearing sarcastic - I do not mean to be and I am doing my
> > > best to help you. Communicating about spreadsheet structure via text
> > > messages can often be difficult and I am sure if I just was there as you
> > > explained and showed it to me I would get it.
> > >
> > > "EED" wrote:
> > >
> > > > I'll try one more time and be simple with it...
> > > >
> > > > for each row...in keeping track of what has been shipped out.
> > > >
> > > > I need to enter the 'shipped amt' - then have it keep a running total in
> > > > 'total shipped' - for that specific line. I have multiple lines/diff part
> > > > numbers. Each line has to keep a running total of total shipped for that
> > > > part/row.
> > > >
> > > > If I only needed to keep a running total on one line, I can do that w/a
> > > > normal accumulator code. But my problem is having this be performed for many
> > > > lines....and again, each row/total has no affect on other totals. I need the
> > > > running total for each line/part.

[K Dales]

If you need the value in column H to keep a running total every time you
enter a new value in column F of the same row (or you could adjust the method
for any 2 cells):

First you need to detect when anything in column F is changed, and some way
of knowing the row. The Worksheet_Change Event Procedure can be used to
detect when there has been a change to any value on the worksheet, so that is
the logical way to do this: put the code in there to do the rest. If you do
not know how to use Event Procedures, I would suggest you ask that in a new
post on this group. I could tell you but it would take more time than I have
now, so perhaps you would get a faster answer through a new inquiry on that.

Now, the code would do the rest, and here is how I would approach it. I
have written it to work even if you copy and paste multiple values at once
into column F (in which case each of those rows need to be updated):

Private Sub Worksheet_Change(ByVal Target As Range)

Dim FRange As Range, ChangedCell As Range
' Find out if Target includes any cells in F:
Set FRange = Intersect(Target, Sheets("Sheet1").Range("F:F"))
' Note: Replace the actual name of the sheet you want checked instead of
"SheetName" in the above
If Not (FRange Is Nothing) Then
' Loop through all the cells in F Range, since these were changed:
For Each ChangedCell In FRange
' Update the value in H (offset 2 columns right) by adding what is in F:
ChangedCell.Offset(0, 2).Value = ChangedCell.Offset(0, 2).Value +
ChangedCell.Value
Next ChangedCell
End If

End Sub

One more thing: You may well have considered this but feel I ought to
mention one weakness of this way of keeping accumulated totals. There is no
way to trace it back - in other words, you can never tell what went into the
total other than the very last value you entered, since all the other values
have been overwritten and are now gone. That may not matter to you, and if
so there is no problem, but felt I ought to point it out since for some
applications it can cause real problems. The usual solution (and one reason
for my confusion earlier!) is to retain prior entries and use a new row for
any new ones, so you have a running list that can keep the running total (but
retaining all prior orders in other rows), with a summary that lists the
current totals for each part (if desired) listed on a separate sheet.

After all the misunderstanding, I hope I finally can be of some help to you!

--
- K Dales


"EED" wrote:

> No, I'm not adding anything from other lines. Each line will be different
> but need to keep a running total for each row/parts shipped. I'd prefer to
> enter in the 'shipped amt for the day' and that amt be added into 'total
> shipped' automatically. Each are individual lines, no other lines affect
> each other. What I enter in for amt shipped on row 1 has nothing to do with
> the amt shipped on row 2-500. Each row has a diff part and qty...but need to
> keep a running total for each row/part #.
>
> Hope this clears the fog and thanks again!
>
> "K Dales" wrote:
>
> > OK, I think I am starting to see and where my misconception was. I was
> > envisioning a system where every separate order shipped, or whatever, was on
> > a separate line and you had to develop a running total from this, or else
> > that each line was a single entry that was entered once and not changed.
> > But that apparently is not correct. I may still be missing a bit, so let's
> > see if I understand: you are not adding anything from another line, but
> > (using the existing line) you are entering a new "Quantity Shipped"
> > (replacing the existing value in that cell) and you want to take that number
> > and add it to whatever was the existing total from that line? And yes, that
> > would be a circular reference if you handled it "normally" through any
> > standard Excel function.
> >
> > I have a thought on how to handle that scenario, if I have it correct, but
> > am just finishing up my lunch break here and need to get back to work! If
> > you read this in the meantime, perhaps you can verify if I have the right
> > concept now of what you need to do - and also, if you could let me know what
> > your VBA/macro experience level is it would help me know how detailed to make
> > the instructions!
> >
> > "EED" wrote:
> >
> > > Running totals will be for each individual line/per part number. These are
> > > all seperate/diff part numbers. So the accumulated total is for total
> > > shipped, in which you will input a daily shipped figure per part. Then that
> > > daily number will be added into the 'total shipped' - again, this is for each
> > > row/part number. I could do this easily if I wanted a circular reference but
> > > not w/the amt of lines I'm working with. I've setup a 2 cell accumulator but
> > > only can get it working for one line, not all the other lines that its needed
> > > on also.
> > >
> > > Hope this helps and thanks!
> > >
> > > "K Dales" wrote:
> > >
> > > > Here is the source of my confusion:
> > > > "I need to enter the 'shipped amt' - then have it keep a running total in
> > > > 'total shipped' - for that specific line." And from an earlier post: "No
> > > > duplicate part
> > > > numbers either and its too keep track of total amt of parts shipped for THAT
> > > > line. Which there could be up to 500 diff lines but needs only keep
> > > > accumulative total per part/line."
> > > >
> > > > "If I only needed to keep a running total on one line, I can do that w/a
> > > > normal accumulator code. But my problem is having this be performed for many
> > > > lines...."
> > > >
> > > > I realize I have badly misinterpreted your question, and still am, but I am
> > > > trying to get past the apparent inconsistency in the above. If the formula
> > > > only acts on the same values for every line (F + H) in the range you gave
> > > > (A1:F500) then do the formula as I stated and simply copy it from cell F2
> > > > down through F500. But if you need to do a running total that does rely on
> > > > the value from another line, or set of lines, I need that clarified to figure
> > > > out how the running total is to be computed.
> > > >
> > > > I hope I am not appearing sarcastic - I do not mean to be and I am doing my
> > > > best to help you. Communicating about spreadsheet structure via text
> > > > messages can often be difficult and I am sure if I just was there as you
> > > > explained and showed it to me I would get it.
> > > >
> > > > "EED" wrote:
> > > >
> > > > > I'll try one more time and be simple with it...
> > > > >
> > > > > for each row...in keeping track of what has been shipped out.
> > > > >
> > > > > I need to enter the 'shipped amt' - then have it keep a running total in
> > > > > 'total shipped' - for that specific line. I have multiple lines/diff part
> > > > > numbers. Each line has to keep a running total of total shipped for that
> > > > > part/row.
> > > > >
> > > > > If I only needed to keep a running total on one line, I can do that w/a
> > > > > normal accumulator code. But my problem is having this be performed for many
> > > > > lines....and again, each row/total has no affect on other totals. I need the
> > > > > running total for each line/part.

[EED]

That is exactly what I needed and worked. Thanks for keeping up with me on
this!


"K Dales" wrote:

> If you need the value in column H to keep a running total every time you
> enter a new value in column F of the same row (or you could adjust the method
> for any 2 cells):
>
> First you need to detect when anything in column F is changed, and some way
> of knowing the row. The Worksheet_Change Event Procedure can be used to
> detect when there has been a change to any value on the worksheet, so that is
> the logical way to do this: put the code in there to do the rest. If you do
> not know how to use Event Procedures, I would suggest you ask that in a new
> post on this group. I could tell you but it would take more time than I have
> now, so perhaps you would get a faster answer through a new inquiry on that.
>
> Now, the code would do the rest, and here is how I would approach it. I
> have written it to work even if you copy and paste multiple values at once
> into column F (in which case each of those rows need to be updated):
>
> Private Sub Worksheet_Change(ByVal Target As Range)
>
> Dim FRange As Range, ChangedCell As Range
> ' Find out if Target includes any cells in F:
> Set FRange = Intersect(Target, Sheets("Sheet1").Range("F:F"))
> ' Note: Replace the actual name of the sheet you want checked instead of
> "SheetName" in the above
> If Not (FRange Is Nothing) Then
> ' Loop through all the cells in F Range, since these were changed:
> For Each ChangedCell In FRange
> ' Update the value in H (offset 2 columns right) by adding what is in F:
> ChangedCell.Offset(0, 2).Value = ChangedCell.Offset(0, 2).Value +
> ChangedCell.Value
> Next ChangedCell
> End If
>
> End Sub
>
> One more thing: You may well have considered this but feel I ought to
> mention one weakness of this way of keeping accumulated totals. There is no
> way to trace it back - in other words, you can never tell what went into the
> total other than the very last value you entered, since all the other values
> have been overwritten and are now gone. That may not matter to you, and if
> so there is no problem, but felt I ought to point it out since for some
> applications it can cause real problems. The usual solution (and one reason
> for my confusion earlier!) is to retain prior entries and use a new row for
> any new ones, so you have a running list that can keep the running total (but
> retaining all prior orders in other rows), with a summary that lists the
> current totals for each part (if desired) listed on a separate sheet.
>
> After all the misunderstanding, I hope I finally can be of some help to you!
>
> --
> - K Dales
>
>
> "EED" wrote:
>
> > No, I'm not adding anything from other lines. Each line will be different
> > but need to keep a running total for each row/parts shipped. I'd prefer to
> > enter in the 'shipped amt for the day' and that amt be added into 'total
> > shipped' automatically. Each are individual lines, no other lines affect
> > each other. What I enter in for amt shipped on row 1 has nothing to do with
> > the amt shipped on row 2-500. Each row has a diff part and qty...but need to
> > keep a running total for each row/part #.
> >
> > Hope this clears the fog and thanks again!
> >
> > "K Dales" wrote:
> >
> > > OK, I think I am starting to see and where my misconception was. I was
> > > envisioning a system where every separate order shipped, or whatever, was on
> > > a separate line and you had to develop a running total from this, or else
> > > that each line was a single entry that was entered once and not changed.
> > > But that apparently is not correct. I may still be missing a bit, so let's
> > > see if I understand: you are not adding anything from another line, but
> > > (using the existing line) you are entering a new "Quantity Shipped"
> > > (replacing the existing value in that cell) and you want to take that number
> > > and add it to whatever was the existing total from that line? And yes, that
> > > would be a circular reference if you handled it "normally" through any
> > > standard Excel function.
> > >
> > > I have a thought on how to handle that scenario, if I have it correct, but
> > > am just finishing up my lunch break here and need to get back to work! If
> > > you read this in the meantime, perhaps you can verify if I have the right
> > > concept now of what you need to do - and also, if you could let me know what
> > > your VBA/macro experience level is it would help me know how detailed to make
> > > the instructions!
> > >
> > > "EED" wrote:
> > >
> > > > Running totals will be for each individual line/per part number. These are
> > > > all seperate/diff part numbers. So the accumulated total is for total
> > > > shipped, in which you will input a daily shipped figure per part. Then that
> > > > daily number will be added into the 'total shipped' - again, this is for each
> > > > row/part number. I could do this easily if I wanted a circular reference but
> > > > not w/the amt of lines I'm working with. I've setup a 2 cell accumulator but
> > > > only can get it working for one line, not all the other lines that its needed
> > > > on also.
> > > >
> > > > Hope this helps and thanks!
> > > >
> > > > "K Dales" wrote:
> > > >
> > > > > Here is the source of my confusion:
> > > > > "I need to enter the 'shipped amt' - then have it keep a running total in
> > > > > 'total shipped' - for that specific line." And from an earlier post: "No
> > > > > duplicate part
> > > > > numbers either and its too keep track of total amt of parts shipped for THAT
> > > > > line. Which there could be up to 500 diff lines but needs only keep
> > > > > accumulative total per part/line."
> > > > >
> > > > > "If I only needed to keep a running total on one line, I can do that w/a
> > > > > normal accumulator code. But my problem is having this be performed for many
> > > > > lines...."
> > > > >
> > > > > I realize I have badly misinterpreted your question, and still am, but I am
> > > > > trying to get past the apparent inconsistency in the above. If the formula
> > > > > only acts on the same values for every line (F + H) in the range you gave
> > > > > (A1:F500) then do the formula as I stated and simply copy it from cell F2
> > > > > down through F500. But if you need to do a running total that does rely on
> > > > > the value from another line, or set of lines, I need that clarified to figure
> > > > > out how the running total is to be computed.
> > > > >
> > > > > I hope I am not appearing sarcastic - I do not mean to be and I am doing my
> > > > > best to help you. Communicating about spreadsheet structure via text
> > > > > messages can often be difficult and I am sure if I just was there as you
> > > > > explained and showed it to me I would get it.
> > > > >
> > > > > "EED" wrote:
> > > > >
> > > > > > I'll try one more time and be simple with it...
> > > > > >
> > > > > > for each row...in keeping track of what has been shipped out.
> > > > > >
> > > > > > I need to enter the 'shipped amt' - then have it keep a running total in
> > > > > > 'total shipped' - for that specific line. I have multiple lines/diff part
> > > > > > numbers. Each line has to keep a running total of total shipped for that
> > > > > > part/row.
> > > > > >
> > > > > > If I only needed to keep a running total on one line, I can do that w/a
> > > > > > normal accumulator code. But my problem is having this be performed for many
> > > > > > lines....and again, each row/total has no affect on other totals. I need the
> > > > > > running total for each line/part.