I have a spreadsheet where I have an "Current" column & a "MTD" column, can I
have the MTD column keep a running total everytime I enter a new number in
the Current column?
I have a spreadsheet where I have an "Current" column & a "MTD" column, can I
have the MTD column keep a running total everytime I enter a new number in
the Current column?
With your current column being B starting with data in B2 and your MTD
column being C
In C2
=IF(B2="","",SUM($B$2:B2))
Copy down column C as far as you need.
--
Regards
Roger Govier
"Anna / Ideal" <[email protected]> wrote in message
news:[email protected]...
>I have a spreadsheet where I have an "Current" column & a "MTD" column, can
>I
> have the MTD column keep a running total everytime I enter a new number in
> the Current column?
Roger: Thank you for your response. My problem is this: When I used this
formula and I typed in a number, that number was shown in the MTD column.
Perfect. Except when I enter a new number in the Current column, it just put
that same number in the MTD column.
Say like this:
Current MTD
Shingle loads 5 10
Job set ups 4 8
Shingle complete 6 15
So, the next day I would just put a new number in the current column and
then I am manually changing the MTD column. I would like it to automatically
add the new number itself in the MTD column.
Is this possible?
"Roger Govier" wrote:
> With your current column being B starting with data in B2 and your MTD
> column being C
> In C2
> =IF(B2="","",SUM($B$2:B2))
> Copy down column C as far as you need.
>
> --
> Regards
>
> Roger Govier
>
>
> "Anna / Ideal" <[email protected]> wrote in message
> news:[email protected]...
> >I have a spreadsheet where I have an "Current" column & a "MTD" column, can
> >I
> > have the MTD column keep a running total everytime I enter a new number in
> > the Current column?
>
>
>
Hi Anna
I don't think you can have copied my formula correctly.
Is your Monthly data in column B? Is your cumulative in column C?
If not you will need to modify it accordingly.
Do note the use of the $ signs in front of the B and 2 at the beginning if
the last expression. They are ther to anchor the start point to the
beginning of you data and will remain fixed as you copy down. The second B2
is relative, and will alter to B3, B4 etc. as you copy down.
=IF(B2="","",SUM($B$2:B2))
Post back iy you re using different columns, and can't amend the formula
yourself.
--
Regards
Roger Govier
"Anna / Ideal" <[email protected]> wrote in message
news:[email protected]...
> Roger: Thank you for your response. My problem is this: When I used
> this
> formula and I typed in a number, that number was shown in the MTD column.
> Perfect. Except when I enter a new number in the Current column, it just
> put
> that same number in the MTD column.
>
> Say like this:
>
> Current MTD
> Shingle loads 5 10
> Job set ups 4 8
> Shingle complete 6 15
>
> So, the next day I would just put a new number in the current column and
> then I am manually changing the MTD column. I would like it to
> automatically
> add the new number itself in the MTD column.
>
> Is this possible?
>
> "Roger Govier" wrote:
>
>> With your current column being B starting with data in B2 and your MTD
>> column being C
>> In C2
>> =IF(B2="","",SUM($B$2:B2))
>> Copy down column C as far as you need.
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "Anna / Ideal" <[email protected]> wrote in message
>> news:[email protected]...
>> >I have a spreadsheet where I have an "Current" column & a "MTD" column,
>> >can
>> >I
>> > have the MTD column keep a running total everytime I enter a new number
>> > in
>> > the Current column?
>>
>>
>>
I beleive I did copy the formula correctly. The thing of it is, is that I
don't think that column C can add to itself. (when I tried to do this myself
I keep getting a circular reference) The number I putting in column B only
copies itself to column C exactly. It doesn't take the number that's already
in column C and add the new number in column B to itself. I'm only getting
what I enter in column B in column C. There is no addition happening.
"Roger Govier" wrote:
> Hi Anna
>
> I don't think you can have copied my formula correctly.
> Is your Monthly data in column B? Is your cumulative in column C?
> If not you will need to modify it accordingly.
>
> Do note the use of the $ signs in front of the B and 2 at the beginning if
> the last expression. They are ther to anchor the start point to the
> beginning of you data and will remain fixed as you copy down. The second B2
> is relative, and will alter to B3, B4 etc. as you copy down.
>
> =IF(B2="","",SUM($B$2:B2))
> Post back iy you re using different columns, and can't amend the formula
> yourself.
>
> --
> Regards
>
> Roger Govier
>
>
> "Anna / Ideal" <[email protected]> wrote in message
> news:[email protected]...
> > Roger: Thank you for your response. My problem is this: When I used
> > this
> > formula and I typed in a number, that number was shown in the MTD column.
> > Perfect. Except when I enter a new number in the Current column, it just
> > put
> > that same number in the MTD column.
> >
> > Say like this:
> >
> > Current MTD
> > Shingle loads 5 10
> > Job set ups 4 8
> > Shingle complete 6 15
> >
> > So, the next day I would just put a new number in the current column and
> > then I am manually changing the MTD column. I would like it to
> > automatically
> > add the new number itself in the MTD column.
> >
> > Is this possible?
> >
> > "Roger Govier" wrote:
> >
> >> With your current column being B starting with data in B2 and your MTD
> >> column being C
> >> In C2
> >> =IF(B2="","",SUM($B$2:B2))
> >> Copy down column C as far as you need.
> >>
> >> --
> >> Regards
> >>
> >> Roger Govier
> >>
> >>
> >> "Anna / Ideal" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I have a spreadsheet where I have an "Current" column & a "MTD" column,
> >> >can
> >> >I
> >> > have the MTD column keep a running total everytime I enter a new number
> >> > in
> >> > the Current column?
> >>
> >>
> >>
>
>
>
Hi Anna
The formula is not adding column C. It is providing a summation of column B
from row 2 down to the last entry in column B that is not blank.
As you continue to enter more data successively down column B, the adjacent
cell in column C will always reflect the summation as described above. Have
you actually tried it, or are you just assuming it doesn't work?
--
Regards
Roger Govier
"Anna / Ideal" <[email protected]> wrote in message
news:[email protected]...
>I beleive I did copy the formula correctly. The thing of it is, is that I
> don't think that column C can add to itself. (when I tried to do this
> myself
> I keep getting a circular reference) The number I putting in column B only
> copies itself to column C exactly. It doesn't take the number that's
> already
> in column C and add the new number in column B to itself. I'm only
> getting
> what I enter in column B in column C. There is no addition happening.
>
> "Roger Govier" wrote:
>
>> Hi Anna
>>
>> I don't think you can have copied my formula correctly.
>> Is your Monthly data in column B? Is your cumulative in column C?
>> If not you will need to modify it accordingly.
>>
>> Do note the use of the $ signs in front of the B and 2 at the beginning
>> if
>> the last expression. They are ther to anchor the start point to the
>> beginning of you data and will remain fixed as you copy down. The second
>> B2
>> is relative, and will alter to B3, B4 etc. as you copy down.
>>
>> =IF(B2="","",SUM($B$2:B2))
>> Post back iy you re using different columns, and can't amend the formula
>> yourself.
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "Anna / Ideal" <[email protected]> wrote in message
>> news:[email protected]...
>> > Roger: Thank you for your response. My problem is this: When I used
>> > this
>> > formula and I typed in a number, that number was shown in the MTD
>> > column.
>> > Perfect. Except when I enter a new number in the Current column, it
>> > just
>> > put
>> > that same number in the MTD column.
>> >
>> > Say like this:
>> >
>> > Current MTD
>> > Shingle loads 5 10
>> > Job set ups 4 8
>> > Shingle complete 6 15
>> >
>> > So, the next day I would just put a new number in the current column
>> > and
>> > then I am manually changing the MTD column. I would like it to
>> > automatically
>> > add the new number itself in the MTD column.
>> >
>> > Is this possible?
>> >
>> > "Roger Govier" wrote:
>> >
>> >> With your current column being B starting with data in B2 and your MTD
>> >> column being C
>> >> In C2
>> >> =IF(B2="","",SUM($B$2:B2))
>> >> Copy down column C as far as you need.
>> >>
>> >> --
>> >> Regards
>> >>
>> >> Roger Govier
>> >>
>> >>
>> >> "Anna / Ideal" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> >I have a spreadsheet where I have an "Current" column & a "MTD"
>> >> >column,
>> >> >can
>> >> >I
>> >> > have the MTD column keep a running total everytime I enter a new
>> >> > number
>> >> > in
>> >> > the Current column?
>> >>
>> >>
>> >>
>>
>>
>>
Yes, you see this is not an ongoing column. I change the number daily for
that particular department. The number I entered yesterday will not be there
today & today's will not be there tomorrow. I just type over the previous
number with the new number. So I am manually keeping the MTD column going.
I just thought there was a way to have that cell in the MTD column C add
itself plus the new number in column B.
"Roger Govier" wrote:
> Hi Anna
>
> The formula is not adding column C. It is providing a summation of column B
> from row 2 down to the last entry in column B that is not blank.
> As you continue to enter more data successively down column B, the adjacent
> cell in column C will always reflect the summation as described above. Have
> you actually tried it, or are you just assuming it doesn't work?
>
> --
> Regards
>
> Roger Govier
>
>
> "Anna / Ideal" <[email protected]> wrote in message
> news:[email protected]...
> >I beleive I did copy the formula correctly. The thing of it is, is that I
> > don't think that column C can add to itself. (when I tried to do this
> > myself
> > I keep getting a circular reference) The number I putting in column B only
> > copies itself to column C exactly. It doesn't take the number that's
> > already
> > in column C and add the new number in column B to itself. I'm only
> > getting
> > what I enter in column B in column C. There is no addition happening.
> >
> > "Roger Govier" wrote:
> >
> >> Hi Anna
> >>
> >> I don't think you can have copied my formula correctly.
> >> Is your Monthly data in column B? Is your cumulative in column C?
> >> If not you will need to modify it accordingly.
> >>
> >> Do note the use of the $ signs in front of the B and 2 at the beginning
> >> if
> >> the last expression. They are ther to anchor the start point to the
> >> beginning of you data and will remain fixed as you copy down. The second
> >> B2
> >> is relative, and will alter to B3, B4 etc. as you copy down.
> >>
> >> =IF(B2="","",SUM($B$2:B2))
> >> Post back iy you re using different columns, and can't amend the formula
> >> yourself.
> >>
> >> --
> >> Regards
> >>
> >> Roger Govier
> >>
> >>
> >> "Anna / Ideal" <[email protected]> wrote in message
> >> news:[email protected]...
> >> > Roger: Thank you for your response. My problem is this: When I used
> >> > this
> >> > formula and I typed in a number, that number was shown in the MTD
> >> > column.
> >> > Perfect. Except when I enter a new number in the Current column, it
> >> > just
> >> > put
> >> > that same number in the MTD column.
> >> >
> >> > Say like this:
> >> >
> >> > Current MTD
> >> > Shingle loads 5 10
> >> > Job set ups 4 8
> >> > Shingle complete 6 15
> >> >
> >> > So, the next day I would just put a new number in the current column
> >> > and
> >> > then I am manually changing the MTD column. I would like it to
> >> > automatically
> >> > add the new number itself in the MTD column.
> >> >
> >> > Is this possible?
> >> >
> >> > "Roger Govier" wrote:
> >> >
> >> >> With your current column being B starting with data in B2 and your MTD
> >> >> column being C
> >> >> In C2
> >> >> =IF(B2="","",SUM($B$2:B2))
> >> >> Copy down column C as far as you need.
> >> >>
> >> >> --
> >> >> Regards
> >> >>
> >> >> Roger Govier
> >> >>
> >> >>
> >> >> "Anna / Ideal" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> >I have a spreadsheet where I have an "Current" column & a "MTD"
> >> >> >column,
> >> >> >can
> >> >> >I
> >> >> > have the MTD column keep a running total everytime I enter a new
> >> >> > number
> >> >> > in
> >> >> > the Current column?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
Hi Anna
I see. The mention of column made me think you had continuous columns of
data.
What you are cahnging and maintaining is just a cell.
Yes you will get circular references if trying to accumultae data.
I think the only solution is a VBA solution - I don't have time to sort this
out for you right now as I have to go out.
--
Regards
Roger Govier
"Anna / Ideal" <[email protected]> wrote in message
news:[email protected]...
> Yes, you see this is not an ongoing column. I change the number daily for
> that particular department. The number I entered yesterday will not be
> there
> today & today's will not be there tomorrow. I just type over the previous
> number with the new number. So I am manually keeping the MTD column
> going.
> I just thought there was a way to have that cell in the MTD column C add
> itself plus the new number in column B.
>
> "Roger Govier" wrote:
>
>> Hi Anna
>>
>> The formula is not adding column C. It is providing a summation of column
>> B
>> from row 2 down to the last entry in column B that is not blank.
>> As you continue to enter more data successively down column B, the
>> adjacent
>> cell in column C will always reflect the summation as described above.
>> Have
>> you actually tried it, or are you just assuming it doesn't work?
>>
>> --
>> Regards
>>
>> Roger Govier
>>
>>
>> "Anna / Ideal" <[email protected]> wrote in message
>> news:[email protected]...
>> >I beleive I did copy the formula correctly. The thing of it is, is that
>> >I
>> > don't think that column C can add to itself. (when I tried to do this
>> > myself
>> > I keep getting a circular reference) The number I putting in column B
>> > only
>> > copies itself to column C exactly. It doesn't take the number that's
>> > already
>> > in column C and add the new number in column B to itself. I'm only
>> > getting
>> > what I enter in column B in column C. There is no addition happening.
>> >
>> > "Roger Govier" wrote:
>> >
>> >> Hi Anna
>> >>
>> >> I don't think you can have copied my formula correctly.
>> >> Is your Monthly data in column B? Is your cumulative in column C?
>> >> If not you will need to modify it accordingly.
>> >>
>> >> Do note the use of the $ signs in front of the B and 2 at the
>> >> beginning
>> >> if
>> >> the last expression. They are ther to anchor the start point to the
>> >> beginning of you data and will remain fixed as you copy down. The
>> >> second
>> >> B2
>> >> is relative, and will alter to B3, B4 etc. as you copy down.
>> >>
>> >> =IF(B2="","",SUM($B$2:B2))
>> >> Post back iy you re using different columns, and can't amend the
>> >> formula
>> >> yourself.
>> >>
>> >> --
>> >> Regards
>> >>
>> >> Roger Govier
>> >>
>> >>
>> >> "Anna / Ideal" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >> > Roger: Thank you for your response. My problem is this: When I
>> >> > used
>> >> > this
>> >> > formula and I typed in a number, that number was shown in the MTD
>> >> > column.
>> >> > Perfect. Except when I enter a new number in the Current column, it
>> >> > just
>> >> > put
>> >> > that same number in the MTD column.
>> >> >
>> >> > Say like this:
>> >> >
>> >> > Current MTD
>> >> > Shingle loads 5 10
>> >> > Job set ups 4 8
>> >> > Shingle complete 6 15
>> >> >
>> >> > So, the next day I would just put a new number in the current column
>> >> > and
>> >> > then I am manually changing the MTD column. I would like it to
>> >> > automatically
>> >> > add the new number itself in the MTD column.
>> >> >
>> >> > Is this possible?
>> >> >
>> >> > "Roger Govier" wrote:
>> >> >
>> >> >> With your current column being B starting with data in B2 and your
>> >> >> MTD
>> >> >> column being C
>> >> >> In C2
>> >> >> =IF(B2="","",SUM($B$2:B2))
>> >> >> Copy down column C as far as you need.
>> >> >>
>> >> >> --
>> >> >> Regards
>> >> >>
>> >> >> Roger Govier
>> >> >>
>> >> >>
>> >> >> "Anna / Ideal" <[email protected]> wrote in
>> >> >> message
>> >> >> news:[email protected]...
>> >> >> >I have a spreadsheet where I have an "Current" column & a "MTD"
>> >> >> >column,
>> >> >> >can
>> >> >> >I
>> >> >> > have the MTD column keep a running total everytime I enter a new
>> >> >> > number
>> >> >> > in
>> >> >> > the Current column?
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>
Well, I thank you so much for all your help!!!!
"Roger Govier" wrote:
> Hi Anna
> I see. The mention of column made me think you had continuous columns of
> data.
> What you are cahnging and maintaining is just a cell.
> Yes you will get circular references if trying to accumultae data.
> I think the only solution is a VBA solution - I don't have time to sort this
> out for you right now as I have to go out.
>
> --
> Regards
>
> Roger Govier
>
>
> "Anna / Ideal" <[email protected]> wrote in message
> news:[email protected]...
> > Yes, you see this is not an ongoing column. I change the number daily for
> > that particular department. The number I entered yesterday will not be
> > there
> > today & today's will not be there tomorrow. I just type over the previous
> > number with the new number. So I am manually keeping the MTD column
> > going.
> > I just thought there was a way to have that cell in the MTD column C add
> > itself plus the new number in column B.
> >
> > "Roger Govier" wrote:
> >
> >> Hi Anna
> >>
> >> The formula is not adding column C. It is providing a summation of column
> >> B
> >> from row 2 down to the last entry in column B that is not blank.
> >> As you continue to enter more data successively down column B, the
> >> adjacent
> >> cell in column C will always reflect the summation as described above.
> >> Have
> >> you actually tried it, or are you just assuming it doesn't work?
> >>
> >> --
> >> Regards
> >>
> >> Roger Govier
> >>
> >>
> >> "Anna / Ideal" <[email protected]> wrote in message
> >> news:[email protected]...
> >> >I beleive I did copy the formula correctly. The thing of it is, is that
> >> >I
> >> > don't think that column C can add to itself. (when I tried to do this
> >> > myself
> >> > I keep getting a circular reference) The number I putting in column B
> >> > only
> >> > copies itself to column C exactly. It doesn't take the number that's
> >> > already
> >> > in column C and add the new number in column B to itself. I'm only
> >> > getting
> >> > what I enter in column B in column C. There is no addition happening.
> >> >
> >> > "Roger Govier" wrote:
> >> >
> >> >> Hi Anna
> >> >>
> >> >> I don't think you can have copied my formula correctly.
> >> >> Is your Monthly data in column B? Is your cumulative in column C?
> >> >> If not you will need to modify it accordingly.
> >> >>
> >> >> Do note the use of the $ signs in front of the B and 2 at the
> >> >> beginning
> >> >> if
> >> >> the last expression. They are ther to anchor the start point to the
> >> >> beginning of you data and will remain fixed as you copy down. The
> >> >> second
> >> >> B2
> >> >> is relative, and will alter to B3, B4 etc. as you copy down.
> >> >>
> >> >> =IF(B2="","",SUM($B$2:B2))
> >> >> Post back iy you re using different columns, and can't amend the
> >> >> formula
> >> >> yourself.
> >> >>
> >> >> --
> >> >> Regards
> >> >>
> >> >> Roger Govier
> >> >>
> >> >>
> >> >> "Anna / Ideal" <[email protected]> wrote in message
> >> >> news:[email protected]...
> >> >> > Roger: Thank you for your response. My problem is this: When I
> >> >> > used
> >> >> > this
> >> >> > formula and I typed in a number, that number was shown in the MTD
> >> >> > column.
> >> >> > Perfect. Except when I enter a new number in the Current column, it
> >> >> > just
> >> >> > put
> >> >> > that same number in the MTD column.
> >> >> >
> >> >> > Say like this:
> >> >> >
> >> >> > Current MTD
> >> >> > Shingle loads 5 10
> >> >> > Job set ups 4 8
> >> >> > Shingle complete 6 15
> >> >> >
> >> >> > So, the next day I would just put a new number in the current column
> >> >> > and
> >> >> > then I am manually changing the MTD column. I would like it to
> >> >> > automatically
> >> >> > add the new number itself in the MTD column.
> >> >> >
> >> >> > Is this possible?
> >> >> >
> >> >> > "Roger Govier" wrote:
> >> >> >
> >> >> >> With your current column being B starting with data in B2 and your
> >> >> >> MTD
> >> >> >> column being C
> >> >> >> In C2
> >> >> >> =IF(B2="","",SUM($B$2:B2))
> >> >> >> Copy down column C as far as you need.
> >> >> >>
> >> >> >> --
> >> >> >> Regards
> >> >> >>
> >> >> >> Roger Govier
> >> >> >>
> >> >> >>
> >> >> >> "Anna / Ideal" <[email protected]> wrote in
> >> >> >> message
> >> >> >> news:[email protected]...
> >> >> >> >I have a spreadsheet where I have an "Current" column & a "MTD"
> >> >> >> >column,
> >> >> >> >can
> >> >> >> >I
> >> >> >> > have the MTD column keep a running total everytime I enter a new
> >> >> >> > number
> >> >> >> > in
> >> >> >> > the Current column?
> >> >> >>
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
You can check out this web page of John McGimpsey, where he gives a VBA as
well as a WS function possible solution to your question:
http://www.mcgimpsey.com/excel/accumulator.html
--
HTH,
RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================
"Anna / Ideal" <[email protected]> wrote in message
news:[email protected]...
> Well, I thank you so much for all your help!!!!
>
> "Roger Govier" wrote:
>
> > Hi Anna
> > I see. The mention of column made me think you had continuous columns of
> > data.
> > What you are cahnging and maintaining is just a cell.
> > Yes you will get circular references if trying to accumultae data.
> > I think the only solution is a VBA solution - I don't have time to sort
this
> > out for you right now as I have to go out.
> >
> > --
> > Regards
> >
> > Roger Govier
> >
> >
> > "Anna / Ideal" <[email protected]> wrote in message
> > news:[email protected]...
> > > Yes, you see this is not an ongoing column. I change the number daily
for
> > > that particular department. The number I entered yesterday will not
be
> > > there
> > > today & today's will not be there tomorrow. I just type over the
previous
> > > number with the new number. So I am manually keeping the MTD column
> > > going.
> > > I just thought there was a way to have that cell in the MTD column C
add
> > > itself plus the new number in column B.
> > >
> > > "Roger Govier" wrote:
> > >
> > >> Hi Anna
> > >>
> > >> The formula is not adding column C. It is providing a summation of
column
> > >> B
> > >> from row 2 down to the last entry in column B that is not blank.
> > >> As you continue to enter more data successively down column B, the
> > >> adjacent
> > >> cell in column C will always reflect the summation as described
above.
> > >> Have
> > >> you actually tried it, or are you just assuming it doesn't work?
> > >>
> > >> --
> > >> Regards
> > >>
> > >> Roger Govier
> > >>
> > >>
> > >> "Anna / Ideal" <[email protected]> wrote in message
> > >> news:[email protected]...
> > >> >I beleive I did copy the formula correctly. The thing of it is, is
that
> > >> >I
> > >> > don't think that column C can add to itself. (when I tried to do
this
> > >> > myself
> > >> > I keep getting a circular reference) The number I putting in column
B
> > >> > only
> > >> > copies itself to column C exactly. It doesn't take the number
that's
> > >> > already
> > >> > in column C and add the new number in column B to itself. I'm only
> > >> > getting
> > >> > what I enter in column B in column C. There is no addition
happening.
> > >> >
> > >> > "Roger Govier" wrote:
> > >> >
> > >> >> Hi Anna
> > >> >>
> > >> >> I don't think you can have copied my formula correctly.
> > >> >> Is your Monthly data in column B? Is your cumulative in column C?
> > >> >> If not you will need to modify it accordingly.
> > >> >>
> > >> >> Do note the use of the $ signs in front of the B and 2 at the
> > >> >> beginning
> > >> >> if
> > >> >> the last expression. They are ther to anchor the start point to
the
> > >> >> beginning of you data and will remain fixed as you copy down. The
> > >> >> second
> > >> >> B2
> > >> >> is relative, and will alter to B3, B4 etc. as you copy down.
> > >> >>
> > >> >> =IF(B2="","",SUM($B$2:B2))
> > >> >> Post back iy you re using different columns, and can't amend the
> > >> >> formula
> > >> >> yourself.
> > >> >>
> > >> >> --
> > >> >> Regards
> > >> >>
> > >> >> Roger Govier
> > >> >>
> > >> >>
> > >> >> "Anna / Ideal" <[email protected]> wrote in
message
> > >> >> news:[email protected]...
> > >> >> > Roger: Thank you for your response. My problem is this: When
I
> > >> >> > used
> > >> >> > this
> > >> >> > formula and I typed in a number, that number was shown in the
MTD
> > >> >> > column.
> > >> >> > Perfect. Except when I enter a new number in the Current
column, it
> > >> >> > just
> > >> >> > put
> > >> >> > that same number in the MTD column.
> > >> >> >
> > >> >> > Say like this:
> > >> >> >
> > >> >> > Current MTD
> > >> >> > Shingle loads 5 10
> > >> >> > Job set ups 4 8
> > >> >> > Shingle complete 6 15
> > >> >> >
> > >> >> > So, the next day I would just put a new number in the current
column
> > >> >> > and
> > >> >> > then I am manually changing the MTD column. I would like it to
> > >> >> > automatically
> > >> >> > add the new number itself in the MTD column.
> > >> >> >
> > >> >> > Is this possible?
> > >> >> >
> > >> >> > "Roger Govier" wrote:
> > >> >> >
> > >> >> >> With your current column being B starting with data in B2 and
your
> > >> >> >> MTD
> > >> >> >> column being C
> > >> >> >> In C2
> > >> >> >> =IF(B2="","",SUM($B$2:B2))
> > >> >> >> Copy down column C as far as you need.
> > >> >> >>
> > >> >> >> --
> > >> >> >> Regards
> > >> >> >>
> > >> >> >> Roger Govier
> > >> >> >>
> > >> >> >>
> > >> >> >> "Anna / Ideal" <[email protected]> wrote in
> > >> >> >> message
> > >> >> >> news:[email protected]...
> > >> >> >> >I have a spreadsheet where I have an "Current" column & a
"MTD"
> > >> >> >> >column,
> > >> >> >> >can
> > >> >> >> >I
> > >> >> >> > have the MTD column keep a running total everytime I enter a
new
> > >> >> >> > number
> > >> >> >> > in
> > >> >> >> > the Current column?
> > >> >> >>
> > >> >> >>
> > >> >> >>
> > >> >>
> > >> >>
> > >> >>
> > >>
> > >>
> > >>
> >
> >
> >
Anna-
You should use two sheets in the same workbook. Use sheet1 to summarize your results for printing and Sheet2 to input the daily numbers .
Set up sheet2 (your data) something like this:
1 A B C D E F G H
2 1/1/2005 1/2/2005 1/3/2005 1/4/2005 1/5/2005 1/6/2005 1/7/2005
3 Item A 7 5 3 8 9 1 4
4 Item B 3 5 9 0 2 4 6
Set up Sheet1 (summary for presentation) as follows:
A B C
1 1/2/2005
2 Cur Mo MTD
3
4 Item A 5 12
5 Item B 5 8
where Cell A1 contains the report date,
The current month column uses the following formula:
=SUMIF(Sheet2!B$2:I$2,Sheet1!$A$1,Sheet2!B3:I3); copy this formula down
Use the following array formula in the month-to-date column:
{=SUM(IF(Sheet2!B$2:I$2<=Sheet1!$A$1,Sheet2!B3:I3,0))}; copy this formula down
Note that this is an array formula - do not type the braces "{}", Excel will enter these for you - you will need to type the formula and press Ctrl-Shift-Enter to make it work.
By setting up your worksheet this way you can "recreate" any day of the month by entering a date into cell A1 on Sheet1.
Also, on Sheet2 enter the first day of the month in Cell B2, then in column C2 enter the formula =B2+1 and copy this to subsequent columns. That way when creating the worksheet for the next month you only need to enter the new month's starting date in B2.
-Terry
Thank you so much for this information. One more question, Can I use this
formula on several cells at a time using the automatic calculation? It seems
if I use this for more than one cell in a column, it will recalculate
everything.
"RagDyer" wrote:
> You can check out this web page of John McGimpsey, where he gives a VBA as
> well as a WS function possible solution to your question:
>
> http://www.mcgimpsey.com/excel/accumulator.html
>
> --
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
>
> "Anna / Ideal" <[email protected]> wrote in message
> news:[email protected]...
> > Well, I thank you so much for all your help!!!!
> >
> > "Roger Govier" wrote:
> >
> > > Hi Anna
> > > I see. The mention of column made me think you had continuous columns of
> > > data.
> > > What you are cahnging and maintaining is just a cell.
> > > Yes you will get circular references if trying to accumultae data.
> > > I think the only solution is a VBA solution - I don't have time to sort
> this
> > > out for you right now as I have to go out.
> > >
> > > --
> > > Regards
> > >
> > > Roger Govier
> > >
> > >
> > > "Anna / Ideal" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Yes, you see this is not an ongoing column. I change the number daily
> for
> > > > that particular department. The number I entered yesterday will not
> be
> > > > there
> > > > today & today's will not be there tomorrow. I just type over the
> previous
> > > > number with the new number. So I am manually keeping the MTD column
> > > > going.
> > > > I just thought there was a way to have that cell in the MTD column C
> add
> > > > itself plus the new number in column B.
> > > >
> > > > "Roger Govier" wrote:
> > > >
> > > >> Hi Anna
> > > >>
> > > >> The formula is not adding column C. It is providing a summation of
> column
> > > >> B
> > > >> from row 2 down to the last entry in column B that is not blank.
> > > >> As you continue to enter more data successively down column B, the
> > > >> adjacent
> > > >> cell in column C will always reflect the summation as described
> above.
> > > >> Have
> > > >> you actually tried it, or are you just assuming it doesn't work?
> > > >>
> > > >> --
> > > >> Regards
> > > >>
> > > >> Roger Govier
> > > >>
> > > >>
> > > >> "Anna / Ideal" <[email protected]> wrote in message
> > > >> news:[email protected]...
> > > >> >I beleive I did copy the formula correctly. The thing of it is, is
> that
> > > >> >I
> > > >> > don't think that column C can add to itself. (when I tried to do
> this
> > > >> > myself
> > > >> > I keep getting a circular reference) The number I putting in column
> B
> > > >> > only
> > > >> > copies itself to column C exactly. It doesn't take the number
> that's
> > > >> > already
> > > >> > in column C and add the new number in column B to itself. I'm only
> > > >> > getting
> > > >> > what I enter in column B in column C. There is no addition
> happening.
> > > >> >
> > > >> > "Roger Govier" wrote:
> > > >> >
> > > >> >> Hi Anna
> > > >> >>
> > > >> >> I don't think you can have copied my formula correctly.
> > > >> >> Is your Monthly data in column B? Is your cumulative in column C?
> > > >> >> If not you will need to modify it accordingly.
> > > >> >>
> > > >> >> Do note the use of the $ signs in front of the B and 2 at the
> > > >> >> beginning
> > > >> >> if
> > > >> >> the last expression. They are ther to anchor the start point to
> the
> > > >> >> beginning of you data and will remain fixed as you copy down. The
> > > >> >> second
> > > >> >> B2
> > > >> >> is relative, and will alter to B3, B4 etc. as you copy down.
> > > >> >>
> > > >> >> =IF(B2="","",SUM($B$2:B2))
> > > >> >> Post back iy you re using different columns, and can't amend the
> > > >> >> formula
> > > >> >> yourself.
> > > >> >>
> > > >> >> --
> > > >> >> Regards
> > > >> >>
> > > >> >> Roger Govier
> > > >> >>
> > > >> >>
> > > >> >> "Anna / Ideal" <[email protected]> wrote in
> message
> > > >> >> news:[email protected]...
> > > >> >> > Roger: Thank you for your response. My problem is this: When
> I
> > > >> >> > used
> > > >> >> > this
> > > >> >> > formula and I typed in a number, that number was shown in the
> MTD
> > > >> >> > column.
> > > >> >> > Perfect. Except when I enter a new number in the Current
> column, it
> > > >> >> > just
> > > >> >> > put
> > > >> >> > that same number in the MTD column.
> > > >> >> >
> > > >> >> > Say like this:
> > > >> >> >
> > > >> >> > Current MTD
> > > >> >> > Shingle loads 5 10
> > > >> >> > Job set ups 4 8
> > > >> >> > Shingle complete 6 15
> > > >> >> >
> > > >> >> > So, the next day I would just put a new number in the current
> column
> > > >> >> > and
> > > >> >> > then I am manually changing the MTD column. I would like it to
> > > >> >> > automatically
> > > >> >> > add the new number itself in the MTD column.
> > > >> >> >
> > > >> >> > Is this possible?
> > > >> >> >
> > > >> >> > "Roger Govier" wrote:
> > > >> >> >
> > > >> >> >> With your current column being B starting with data in B2 and
> your
> > > >> >> >> MTD
> > > >> >> >> column being C
> > > >> >> >> In C2
> > > >> >> >> =IF(B2="","",SUM($B$2:B2))
> > > >> >> >> Copy down column C as far as you need.
> > > >> >> >>
> > > >> >> >> --
> > > >> >> >> Regards
> > > >> >> >>
> > > >> >> >> Roger Govier
> > > >> >> >>
> > > >> >> >>
> > > >> >> >> "Anna / Ideal" <[email protected]> wrote in
> > > >> >> >> message
> > > >> >> >> news:[email protected]...
> > > >> >> >> >I have a spreadsheet where I have an "Current" column & a
> "MTD"
> > > >> >> >> >column,
> > > >> >> >> >can
> > > >> >> >> >I
> > > >> >> >> > have the MTD column keep a running total everytime I enter a
> new
> > > >> >> >> > number
> > > >> >> >> > in
> > > >> >> >> > the Current column?
> > > >> >> >>
> > > >> >> >>
> > > >> >> >>
> > > >> >>
> > > >> >>
> > > >> >>
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >
>
>
AFAIK,
Automatic calculation will change (update) all such accumulator cells in the
*entire* WB.
The only thing I can think of, is to place these cells on different sheets.
Then, you can set calculation to "manual", and create new windows, so that
each sheet is concurrently displayed alongside each other.
Then, you can manually calculate *individual* sheets, thus revising one
accumulator, without changing the others.
<Shift> <F9> will calculate *only* the sheet in focus.
--
HTH,
RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================
"Anna / Ideal" <[email protected]> wrote in message
news:[email protected]...
Thank you so much for this information. One more question, Can I use this
formula on several cells at a time using the automatic calculation? It
seems
if I use this for more than one cell in a column, it will recalculate
everything.
"RagDyer" wrote:
> You can check out this web page of John McGimpsey, where he gives a VBA as
> well as a WS function possible solution to your question:
>
> http://www.mcgimpsey.com/excel/accumulator.html
>
> --
> HTH,
>
> RD
> ==============================================
> Please keep all correspondence within the Group, so all may benefit!
> ==============================================
>
>
> "Anna / Ideal" <[email protected]> wrote in message
> news:[email protected]...
> > Well, I thank you so much for all your help!!!!
> >
> > "Roger Govier" wrote:
> >
> > > Hi Anna
> > > I see. The mention of column made me think you had continuous columns
of
> > > data.
> > > What you are cahnging and maintaining is just a cell.
> > > Yes you will get circular references if trying to accumultae data.
> > > I think the only solution is a VBA solution - I don't have time to
sort
> this
> > > out for you right now as I have to go out.
> > >
> > > --
> > > Regards
> > >
> > > Roger Govier
> > >
> > >
> > > "Anna / Ideal" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Yes, you see this is not an ongoing column. I change the number
daily
> for
> > > > that particular department. The number I entered yesterday will not
> be
> > > > there
> > > > today & today's will not be there tomorrow. I just type over the
> previous
> > > > number with the new number. So I am manually keeping the MTD column
> > > > going.
> > > > I just thought there was a way to have that cell in the MTD column C
> add
> > > > itself plus the new number in column B.
> > > >
> > > > "Roger Govier" wrote:
> > > >
> > > >> Hi Anna
> > > >>
> > > >> The formula is not adding column C. It is providing a summation of
> column
> > > >> B
> > > >> from row 2 down to the last entry in column B that is not blank.
> > > >> As you continue to enter more data successively down column B, the
> > > >> adjacent
> > > >> cell in column C will always reflect the summation as described
> above.
> > > >> Have
> > > >> you actually tried it, or are you just assuming it doesn't work?
> > > >>
> > > >> --
> > > >> Regards
> > > >>
> > > >> Roger Govier
> > > >>
> > > >>
> > > >> "Anna / Ideal" <[email protected]> wrote in
message
> > > >> news:[email protected]...
> > > >> >I beleive I did copy the formula correctly. The thing of it is,
is
> that
> > > >> >I
> > > >> > don't think that column C can add to itself. (when I tried to do
> this
> > > >> > myself
> > > >> > I keep getting a circular reference) The number I putting in
column
> B
> > > >> > only
> > > >> > copies itself to column C exactly. It doesn't take the number
> that's
> > > >> > already
> > > >> > in column C and add the new number in column B to itself. I'm
only
> > > >> > getting
> > > >> > what I enter in column B in column C. There is no addition
> happening.
> > > >> >
> > > >> > "Roger Govier" wrote:
> > > >> >
> > > >> >> Hi Anna
> > > >> >>
> > > >> >> I don't think you can have copied my formula correctly.
> > > >> >> Is your Monthly data in column B? Is your cumulative in column
C?
> > > >> >> If not you will need to modify it accordingly.
> > > >> >>
> > > >> >> Do note the use of the $ signs in front of the B and 2 at the
> > > >> >> beginning
> > > >> >> if
> > > >> >> the last expression. They are ther to anchor the start point to
> the
> > > >> >> beginning of you data and will remain fixed as you copy down.
The
> > > >> >> second
> > > >> >> B2
> > > >> >> is relative, and will alter to B3, B4 etc. as you copy down.
> > > >> >>
> > > >> >> =IF(B2="","",SUM($B$2:B2))
> > > >> >> Post back iy you re using different columns, and can't amend the
> > > >> >> formula
> > > >> >> yourself.
> > > >> >>
> > > >> >> --
> > > >> >> Regards
> > > >> >>
> > > >> >> Roger Govier
> > > >> >>
> > > >> >>
> > > >> >> "Anna / Ideal" <[email protected]> wrote in
> message
> > > >> >> news:[email protected]...
> > > >> >> > Roger: Thank you for your response. My problem is this:
When
> I
> > > >> >> > used
> > > >> >> > this
> > > >> >> > formula and I typed in a number, that number was shown in the
> MTD
> > > >> >> > column.
> > > >> >> > Perfect. Except when I enter a new number in the Current
> column, it
> > > >> >> > just
> > > >> >> > put
> > > >> >> > that same number in the MTD column.
> > > >> >> >
> > > >> >> > Say like this:
> > > >> >> >
> > > >> >> > Current MTD
> > > >> >> > Shingle loads 5 10
> > > >> >> > Job set ups 4 8
> > > >> >> > Shingle complete 6 15
> > > >> >> >
> > > >> >> > So, the next day I would just put a new number in the current
> column
> > > >> >> > and
> > > >> >> > then I am manually changing the MTD column. I would like it
to
> > > >> >> > automatically
> > > >> >> > add the new number itself in the MTD column.
> > > >> >> >
> > > >> >> > Is this possible?
> > > >> >> >
> > > >> >> > "Roger Govier" wrote:
> > > >> >> >
> > > >> >> >> With your current column being B starting with data in B2 and
> your
> > > >> >> >> MTD
> > > >> >> >> column being C
> > > >> >> >> In C2
> > > >> >> >> =IF(B2="","",SUM($B$2:B2))
> > > >> >> >> Copy down column C as far as you need.
> > > >> >> >>
> > > >> >> >> --
> > > >> >> >> Regards
> > > >> >> >>
> > > >> >> >> Roger Govier
> > > >> >> >>
> > > >> >> >>
> > > >> >> >> "Anna / Ideal" <[email protected]> wrote in
> > > >> >> >> message
> > > >> >> >> news:[email protected]...
> > > >> >> >> >I have a spreadsheet where I have an "Current" column & a
> "MTD"
> > > >> >> >> >column,
> > > >> >> >> >can
> > > >> >> >> >I
> > > >> >> >> > have the MTD column keep a running total everytime I enter
a
> new
> > > >> >> >> > number
> > > >> >> >> > in
> > > >> >> >> > the Current column?
> > > >> >> >>
> > > >> >> >>
> > > >> >> >>
> > > >> >>
> > > >> >>
> > > >> >>
> > > >>
> > > >>
> > > >>
> > >
> > >
> > >
>
>
Ok, I will try that. You have helped me so much. I appreciate your time.
Anna
"RagDyeR" wrote:
> AFAIK,
>
> Automatic calculation will change (update) all such accumulator cells in the
> *entire* WB.
>
> The only thing I can think of, is to place these cells on different sheets.
> Then, you can set calculation to "manual", and create new windows, so that
> each sheet is concurrently displayed alongside each other.
>
> Then, you can manually calculate *individual* sheets, thus revising one
> accumulator, without changing the others.
>
> <Shift> <F9> will calculate *only* the sheet in focus.
> --
>
> HTH,
>
> RD
> =====================================================
> Please keep all correspondence within the Group, so all may benefit!
> =====================================================
>
> "Anna / Ideal" <[email protected]> wrote in message
> news:[email protected]...
> Thank you so much for this information. One more question, Can I use this
> formula on several cells at a time using the automatic calculation? It
> seems
> if I use this for more than one cell in a column, it will recalculate
> everything.
>
> "RagDyer" wrote:
>
> > You can check out this web page of John McGimpsey, where he gives a VBA as
> > well as a WS function possible solution to your question:
> >
> > http://www.mcgimpsey.com/excel/accumulator.html
> >
> > --
> > HTH,
> >
> > RD
> > ==============================================
> > Please keep all correspondence within the Group, so all may benefit!
> > ==============================================
> >
> >
> > "Anna / Ideal" <[email protected]> wrote in message
> > news:[email protected]...
> > > Well, I thank you so much for all your help!!!!
> > >
> > > "Roger Govier" wrote:
> > >
> > > > Hi Anna
> > > > I see. The mention of column made me think you had continuous columns
> of
> > > > data.
> > > > What you are cahnging and maintaining is just a cell.
> > > > Yes you will get circular references if trying to accumultae data.
> > > > I think the only solution is a VBA solution - I don't have time to
> sort
> > this
> > > > out for you right now as I have to go out.
> > > >
> > > > --
> > > > Regards
> > > >
> > > > Roger Govier
> > > >
> > > >
> > > > "Anna / Ideal" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Yes, you see this is not an ongoing column. I change the number
> daily
> > for
> > > > > that particular department. The number I entered yesterday will not
> > be
> > > > > there
> > > > > today & today's will not be there tomorrow. I just type over the
> > previous
> > > > > number with the new number. So I am manually keeping the MTD column
> > > > > going.
> > > > > I just thought there was a way to have that cell in the MTD column C
> > add
> > > > > itself plus the new number in column B.
> > > > >
> > > > > "Roger Govier" wrote:
> > > > >
> > > > >> Hi Anna
> > > > >>
> > > > >> The formula is not adding column C. It is providing a summation of
> > column
> > > > >> B
> > > > >> from row 2 down to the last entry in column B that is not blank.
> > > > >> As you continue to enter more data successively down column B, the
> > > > >> adjacent
> > > > >> cell in column C will always reflect the summation as described
> > above.
> > > > >> Have
> > > > >> you actually tried it, or are you just assuming it doesn't work?
> > > > >>
> > > > >> --
> > > > >> Regards
> > > > >>
> > > > >> Roger Govier
> > > > >>
> > > > >>
> > > > >> "Anna / Ideal" <[email protected]> wrote in
> message
> > > > >> news:[email protected]...
> > > > >> >I beleive I did copy the formula correctly. The thing of it is,
> is
> > that
> > > > >> >I
> > > > >> > don't think that column C can add to itself. (when I tried to do
> > this
> > > > >> > myself
> > > > >> > I keep getting a circular reference) The number I putting in
> column
> > B
> > > > >> > only
> > > > >> > copies itself to column C exactly. It doesn't take the number
> > that's
> > > > >> > already
> > > > >> > in column C and add the new number in column B to itself. I'm
> only
> > > > >> > getting
> > > > >> > what I enter in column B in column C. There is no addition
> > happening.
> > > > >> >
> > > > >> > "Roger Govier" wrote:
> > > > >> >
> > > > >> >> Hi Anna
> > > > >> >>
> > > > >> >> I don't think you can have copied my formula correctly.
> > > > >> >> Is your Monthly data in column B? Is your cumulative in column
> C?
> > > > >> >> If not you will need to modify it accordingly.
> > > > >> >>
> > > > >> >> Do note the use of the $ signs in front of the B and 2 at the
> > > > >> >> beginning
> > > > >> >> if
> > > > >> >> the last expression. They are ther to anchor the start point to
> > the
> > > > >> >> beginning of you data and will remain fixed as you copy down.
> The
> > > > >> >> second
> > > > >> >> B2
> > > > >> >> is relative, and will alter to B3, B4 etc. as you copy down.
> > > > >> >>
> > > > >> >> =IF(B2="","",SUM($B$2:B2))
> > > > >> >> Post back iy you re using different columns, and can't amend the
> > > > >> >> formula
> > > > >> >> yourself.
> > > > >> >>
> > > > >> >> --
> > > > >> >> Regards
> > > > >> >>
> > > > >> >> Roger Govier
> > > > >> >>
> > > > >> >>
> > > > >> >> "Anna / Ideal" <[email protected]> wrote in
> > message
> > > > >> >> news:[email protected]...
> > > > >> >> > Roger: Thank you for your response. My problem is this:
> When
> > I
> > > > >> >> > used
> > > > >> >> > this
> > > > >> >> > formula and I typed in a number, that number was shown in the
> > MTD
> > > > >> >> > column.
> > > > >> >> > Perfect. Except when I enter a new number in the Current
> > column, it
> > > > >> >> > just
> > > > >> >> > put
> > > > >> >> > that same number in the MTD column.
> > > > >> >> >
> > > > >> >> > Say like this:
> > > > >> >> >
> > > > >> >> > Current MTD
> > > > >> >> > Shingle loads 5 10
> > > > >> >> > Job set ups 4 8
> > > > >> >> > Shingle complete 6 15
> > > > >> >> >
> > > > >> >> > So, the next day I would just put a new number in the current
> > column
> > > > >> >> > and
> > > > >> >> > then I am manually changing the MTD column. I would like it
> to
> > > > >> >> > automatically
> > > > >> >> > add the new number itself in the MTD column.
> > > > >> >> >
> > > > >> >> > Is this possible?
> > > > >> >> >
> > > > >> >> > "Roger Govier" wrote:
> > > > >> >> >
> > > > >> >> >> With your current column being B starting with data in B2 and
> > your
> > > > >> >> >> MTD
> > > > >> >> >> column being C
> > > > >> >> >> In C2
> > > > >> >> >> =IF(B2="","",SUM($B$2:B2))
> > > > >> >> >> Copy down column C as far as you need.
> > > > >> >> >>
> > > > >> >> >> --
> > > > >> >> >> Regards
> > > > >> >> >>
> > > > >> >> >> Roger Govier
> > > > >> >> >>
> > > > >> >> >>
> > > > >> >> >> "Anna / Ideal" <[email protected]> wrote in
> > > > >> >> >> message
> > > > >> >> >> news:[email protected]...
> > > > >> >> >> >I have a spreadsheet where I have an "Current" column & a
> > "MTD"
> > > > >> >> >> >column,
> > > > >> >> >> >can
> > > > >> >> >> >I
> > > > >> >> >> > have the MTD column keep a running total everytime I enter
> a
> > new
> > > > >> >> >> > number
> > > > >> >> >> > in
> > > > >> >> >> > the Current column?
> > > > >> >> >>
> > > > >> >> >>
> > > > >> >> >>
> > > > >> >>
> > > > >> >>
> > > > >> >>
> > > > >>
> > > > >>
> > > > >>
> > > >
> > > >
> > > >
> >
> >
>
>
>
Hope it works out for you.
--
Regards,
RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------
"Anna / Ideal" <[email protected]> wrote in message
news:[email protected]...
Ok, I will try that. You have helped me so much. I appreciate your time.
Anna
"RagDyeR" wrote:
> AFAIK,
>
> Automatic calculation will change (update) all such accumulator cells in
the
> *entire* WB.
>
> The only thing I can think of, is to place these cells on different
sheets.
> Then, you can set calculation to "manual", and create new windows, so that
> each sheet is concurrently displayed alongside each other.
>
> Then, you can manually calculate *individual* sheets, thus revising one
> accumulator, without changing the others.
>
> <Shift> <F9> will calculate *only* the sheet in focus.
> --
>
> HTH,
>
> RD
> =====================================================
> Please keep all correspondence within the Group, so all may benefit!
> =====================================================
>
> "Anna / Ideal" <[email protected]> wrote in message
> news:[email protected]...
> Thank you so much for this information. One more question, Can I use this
> formula on several cells at a time using the automatic calculation? It
> seems
> if I use this for more than one cell in a column, it will recalculate
> everything.
>
> "RagDyer" wrote:
>
> > You can check out this web page of John McGimpsey, where he gives a VBA
as
> > well as a WS function possible solution to your question:
> >
> > http://www.mcgimpsey.com/excel/accumulator.html
> >
> > --
> > HTH,
> >
> > RD
> > ==============================================
> > Please keep all correspondence within the Group, so all may benefit!
> > ==============================================
> >
> >
> > "Anna / Ideal" <[email protected]> wrote in message
> > news:[email protected]...
> > > Well, I thank you so much for all your help!!!!
> > >
> > > "Roger Govier" wrote:
> > >
> > > > Hi Anna
> > > > I see. The mention of column made me think you had continuous
columns
> of
> > > > data.
> > > > What you are cahnging and maintaining is just a cell.
> > > > Yes you will get circular references if trying to accumultae data.
> > > > I think the only solution is a VBA solution - I don't have time to
> sort
> > this
> > > > out for you right now as I have to go out.
> > > >
> > > > --
> > > > Regards
> > > >
> > > > Roger Govier
> > > >
> > > >
> > > > "Anna / Ideal" <[email protected]> wrote in
message
> > > > news:[email protected]...
> > > > > Yes, you see this is not an ongoing column. I change the number
> daily
> > for
> > > > > that particular department. The number I entered yesterday will
not
> > be
> > > > > there
> > > > > today & today's will not be there tomorrow. I just type over the
> > previous
> > > > > number with the new number. So I am manually keeping the MTD
column
> > > > > going.
> > > > > I just thought there was a way to have that cell in the MTD column
C
> > add
> > > > > itself plus the new number in column B.
> > > > >
> > > > > "Roger Govier" wrote:
> > > > >
> > > > >> Hi Anna
> > > > >>
> > > > >> The formula is not adding column C. It is providing a summation
of
> > column
> > > > >> B
> > > > >> from row 2 down to the last entry in column B that is not blank.
> > > > >> As you continue to enter more data successively down column B,
the
> > > > >> adjacent
> > > > >> cell in column C will always reflect the summation as described
> > above.
> > > > >> Have
> > > > >> you actually tried it, or are you just assuming it doesn't work?
> > > > >>
> > > > >> --
> > > > >> Regards
> > > > >>
> > > > >> Roger Govier
> > > > >>
> > > > >>
> > > > >> "Anna / Ideal" <[email protected]> wrote in
> message
> > > > >> news:[email protected]...
> > > > >> >I beleive I did copy the formula correctly. The thing of it is,
> is
> > that
> > > > >> >I
> > > > >> > don't think that column C can add to itself. (when I tried to
do
> > this
> > > > >> > myself
> > > > >> > I keep getting a circular reference) The number I putting in
> column
> > B
> > > > >> > only
> > > > >> > copies itself to column C exactly. It doesn't take the number
> > that's
> > > > >> > already
> > > > >> > in column C and add the new number in column B to itself. I'm
> only
> > > > >> > getting
> > > > >> > what I enter in column B in column C. There is no addition
> > happening.
> > > > >> >
> > > > >> > "Roger Govier" wrote:
> > > > >> >
> > > > >> >> Hi Anna
> > > > >> >>
> > > > >> >> I don't think you can have copied my formula correctly.
> > > > >> >> Is your Monthly data in column B? Is your cumulative in column
> C?
> > > > >> >> If not you will need to modify it accordingly.
> > > > >> >>
> > > > >> >> Do note the use of the $ signs in front of the B and 2 at the
> > > > >> >> beginning
> > > > >> >> if
> > > > >> >> the last expression. They are ther to anchor the start point
to
> > the
> > > > >> >> beginning of you data and will remain fixed as you copy down.
> The
> > > > >> >> second
> > > > >> >> B2
> > > > >> >> is relative, and will alter to B3, B4 etc. as you copy down.
> > > > >> >>
> > > > >> >> =IF(B2="","",SUM($B$2:B2))
> > > > >> >> Post back iy you re using different columns, and can't amend
the
> > > > >> >> formula
> > > > >> >> yourself.
> > > > >> >>
> > > > >> >> --
> > > > >> >> Regards
> > > > >> >>
> > > > >> >> Roger Govier
> > > > >> >>
> > > > >> >>
> > > > >> >> "Anna / Ideal" <[email protected]> wrote in
> > message
> > > > >> >> news:[email protected]...
> > > > >> >> > Roger: Thank you for your response. My problem is this:
> When
> > I
> > > > >> >> > used
> > > > >> >> > this
> > > > >> >> > formula and I typed in a number, that number was shown in
the
> > MTD
> > > > >> >> > column.
> > > > >> >> > Perfect. Except when I enter a new number in the Current
> > column, it
> > > > >> >> > just
> > > > >> >> > put
> > > > >> >> > that same number in the MTD column.
> > > > >> >> >
> > > > >> >> > Say like this:
> > > > >> >> >
> > > > >> >> > Current MTD
> > > > >> >> > Shingle loads 5 10
> > > > >> >> > Job set ups 4 8
> > > > >> >> > Shingle complete 6 15
> > > > >> >> >
> > > > >> >> > So, the next day I would just put a new number in the
current
> > column
> > > > >> >> > and
> > > > >> >> > then I am manually changing the MTD column. I would like it
> to
> > > > >> >> > automatically
> > > > >> >> > add the new number itself in the MTD column.
> > > > >> >> >
> > > > >> >> > Is this possible?
> > > > >> >> >
> > > > >> >> > "Roger Govier" wrote:
> > > > >> >> >
> > > > >> >> >> With your current column being B starting with data in B2
and
> > your
> > > > >> >> >> MTD
> > > > >> >> >> column being C
> > > > >> >> >> In C2
> > > > >> >> >> =IF(B2="","",SUM($B$2:B2))
> > > > >> >> >> Copy down column C as far as you need.
> > > > >> >> >>
> > > > >> >> >> --
> > > > >> >> >> Regards
> > > > >> >> >>
> > > > >> >> >> Roger Govier
> > > > >> >> >>
> > > > >> >> >>
> > > > >> >> >> "Anna / Ideal" <[email protected]> wrote
in
> > > > >> >> >> message
> > > > >> >> >> news:[email protected]...
> > > > >> >> >> >I have a spreadsheet where I have an "Current" column & a
> > "MTD"
> > > > >> >> >> >column,
> > > > >> >> >> >can
> > > > >> >> >> >I
> > > > >> >> >> > have the MTD column keep a running total everytime I
enter
> a
> > new
> > > > >> >> >> > number
> > > > >> >> >> > in
> > > > >> >> >> > the Current column?
> > > > >> >> >>
> > > > >> >> >>
> > > > >> >> >>
> > > > >> >>
> > > > >> >>
> > > > >> >>
> > > > >>
> > > > >>
> > > > >>
> > > >
> > > >
> > > >
> >
> >
>
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks