How can I get numbers in one column (ie Year-to-date) to automatically update
when I add new numbers in another column (ie Month-to-date)?
How can I get numbers in one column (ie Year-to-date) to automatically update
when I add new numbers in another column (ie Month-to-date)?
Hi
do you have
...........A...........B...............C............M........N
1.......Item......Jan.............Feb..........Dec.....YTD
if so the formula in N2 would be =SUM(B2:M2)
or do you have
.........A...........B..............C
1.....Item.......Mth Value...YTD Value
if so the formula in C2 would be
=B2+C2
but you'll have to choose tools / options / calculation and tick the
Iterations box and set the maximum value to 1
Cheers
JulieD
"rosebud_28" <[email protected]> wrote in message
news:[email protected]...
> How can I get numbers in one column (ie Year-to-date) to automatically
> update
> when I add new numbers in another column (ie Month-to-date)?
My example is like #2.
Name.......MTD.....YTD
I changed the iteraion values as indicated, but now when I enter my formula
in the second row down it doubles the first row amount.
example MTD YTD
doe, john........30,000............30,000(=b2+c2)
smith, don.......20,000...........when I enter the same formula here my
30,000 turns into 60,000. why?
"JulieD" wrote:
> Hi
>
> do you have
> ...........A...........B...............C............M........N
> 1.......Item......Jan.............Feb..........Dec.....YTD
>
> if so the formula in N2 would be =SUM(B2:M2)
>
> or do you have
> .........A...........B..............C
> 1.....Item.......Mth Value...YTD Value
>
> if so the formula in C2 would be
> =B2+C2
> but you'll have to choose tools / options / calculation and tick the
> Iterations box and set the maximum value to 1
>
> Cheers
> JulieD
>
> "rosebud_28" <[email protected]> wrote in message
> news:[email protected]...
> > How can I get numbers in one column (ie Year-to-date) to automatically
> > update
> > when I add new numbers in another column (ie Month-to-date)?
>
>
>
I am still looking for help on this worksheet. I would appreciate a response
from anyone who could assist me. Please.
"Automatically updating" wrote:
> My example is like #2.
> Name.......MTD.....YTD
>
> I changed the iteraion values as indicated, but now when I enter my formula
> in the second row down it doubles the first row amount.
> example MTD YTD
> doe, john........30,000............30,000(=b2+c2)
> smith, don.......20,000...........when I enter the same formula here my
> 30,000 turns into 60,000. why?
>
> "JulieD" wrote:
>
> > Hi
> >
> > do you have
> > ...........A...........B...............C............M........N
> > 1.......Item......Jan.............Feb..........Dec.....YTD
> >
> > if so the formula in N2 would be =SUM(B2:M2)
> >
> > or do you have
> > .........A...........B..............C
> > 1.....Item.......Mth Value...YTD Value
> >
> > if so the formula in C2 would be
> > =B2+C2
> > but you'll have to choose tools / options / calculation and tick the
> > Iterations box and set the maximum value to 1
> >
> > Cheers
> > JulieD
> >
> > "rosebud_28" <[email protected]> wrote in message
> > news:[email protected]...
> > > How can I get numbers in one column (ie Year-to-date) to automatically
> > > update
> > > when I add new numbers in another column (ie Month-to-date)?
> >
> >
> >
If you entered
=B2+C2
into C3, then you told the formula to add B2 (30,000) and C2 (30,000).
If you only want it to sum Smith's values, use
=B3+C3
Note that accumulators like this can be very difficult to edit and
audit.
For additional types of accumulators, you can check out
http://www.mcgimpsey.com/excel/accumulator.html
In article <[email protected]>,
"Automatically updating"
<[email protected]> wrote:
> I am still looking for help on this worksheet. I would appreciate a response
> from anyone who could assist me. Please.
>
> "Automatically updating" wrote:
>
> > My example is like #2.
> > Name.......MTD.....YTD
> >
> > I changed the iteraion values as indicated, but now when I enter my formula
> > in the second row down it doubles the first row amount.
> > example MTD YTD
> > doe, john........30,000............30,000(=b2+c2)
> > smith, don.......20,000...........when I enter the same formula here my
> > 30,000 turns into 60,000. why?
I keep getting the same formula from everyone, but it is not working. It
works as long as I enter it in just the first row. But, when I enter the
same formula for the next row (changing the row # of course) it doubles the
amount in the row above it.
HELP!
"JE McGimpsey" wrote:
> If you entered
>
> =B2+C2
>
> into C3, then you told the formula to add B2 (30,000) and C2 (30,000).
>
> If you only want it to sum Smith's values, use
>
> =B3+C3
>
> Note that accumulators like this can be very difficult to edit and
> audit.
>
> For additional types of accumulators, you can check out
>
> http://www.mcgimpsey.com/excel/accumulator.html
>
>
>
> In article <[email protected]>,
> "Automatically updating"
> <[email protected]> wrote:
>
> > I am still looking for help on this worksheet. I would appreciate a response
> > from anyone who could assist me. Please.
> >
> > "Automatically updating" wrote:
> >
> > > My example is like #2.
> > > Name.......MTD.....YTD
> > >
> > > I changed the iteraion values as indicated, but now when I enter my formula
> > > in the second row down it doubles the first row amount.
> > > example MTD YTD
> > > doe, john........30,000............30,000(=b2+c2)
> > > smith, don.......20,000...........when I enter the same formula here my
> > > 30,000 turns into 60,000. why?
>
The formula =B2+C2 may not be the best solution. Also, when you enter it
into another row manually, you need to manually update the row references,
or use Copy and Paste to get the row references to update.
Usually, an accumulator is used like
Col B Col C
Monthly Yearly (Row 1)
100 100
200 300
and the formulas would be (in cell C2)
=B2
and in cell C3, you would use the formula
=B3+C2
and _copy_ that cell down the column so the next row has = B4+C3, etc....
HTH,
Bernie
MS Excel MVP
"Automatically updating" <[email protected]>
wrote in message news:[email protected]...
> I keep getting the same formula from everyone, but it is not working. It
> works as long as I enter it in just the first row. But, when I enter the
> same formula for the next row (changing the row # of course) it doubles
the
> amount in the row above it.
> HELP!
>
> "JE McGimpsey" wrote:
>
> > If you entered
> >
> > =B2+C2
> >
> > into C3, then you told the formula to add B2 (30,000) and C2 (30,000).
> >
> > If you only want it to sum Smith's values, use
> >
> > =B3+C3
> >
> > Note that accumulators like this can be very difficult to edit and
> > audit.
> >
> > For additional types of accumulators, you can check out
> >
> > http://www.mcgimpsey.com/excel/accumulator.html
> >
> >
> >
> > In article <[email protected]>,
> > "Automatically updating"
> > <[email protected]> wrote:
> >
> > > I am still looking for help on this worksheet. I would appreciate a
response
> > > from anyone who could assist me. Please.
> > >
> > > "Automatically updating" wrote:
> > >
> > > > My example is like #2.
> > > > Name.......MTD.....YTD
> > > >
> > > > I changed the iteraion values as indicated, but now when I enter my
formula
> > > > in the second row down it doubles the first row amount.
> > > > example MTD YTD
> > > > doe, john........30,000............30,000(=b2+c2)
> > > > smith, don.......20,000...........when I enter the same formula here
my
> > > > 30,000 turns into 60,000. why?
> >
Automatically updating wrote:
> My example is like #2.
> Name.......MTD.....YTD
>
> I changed the iteraion values as indicated, but now when I enter my formula
> in the second row down it doubles the first row amount.
> example MTD YTD
> doe, john........30,000............30,000(=b2+c2)
> smith, don.......20,000...........when I enter the same formula here my
> 30,000 turns into 60,000. why?
>
> "JulieD" wrote:
>
>
>>Hi
>>
>>do you have
>>...........A...........B...............C............M........N
>>1.......Item......Jan.............Feb..........Dec.....YTD
>>
>>if so the formula in N2 would be =SUM(B2:M2)
>>
>>or do you have
>>.........A...........B..............C
>>1.....Item.......Mth Value...YTD Value
>>
>>if so the formula in C2 would be
>>=B2+C2
>>but you'll have to choose tools / options / calculation and tick the
>>Iterations box and set the maximum value to 1
>>
>>Cheers
>>JulieD
>>
>>"rosebud_28" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>How can I get numbers in one column (ie Year-to-date) to automatically
>>>update
>>>when I add new numbers in another column (ie Month-to-date)?
>>
>>
>>
I would recommend having a column for each month with a YTD column
that way you can sum each row for a YTD and you will be able to see
at a glance what the MTD total was for each person in each month.
You could then sum the YTD column to get a total YTD for all rows.
Just seems a little more informative and easier to maintain to me.
You could always hide the previous month columns if you didn't want
to see them.
gls858
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks