Updating YTD summation monthly

  • Thread starter Thread starter Sean
  • Start date Start date
S

Sean

I have a need to update YTD sum of a range monthly.
Current setup as follows:
A B C D E F G H Z
1 Jan Feb Mar Apr May Jun JulOL AugOL ...YTDJun
2 a2 b2 c2 d2 e2 f2 g2 h2 z2
3 a3 b3 c3 d3 e3 f3 g3 h3 z3

where cell z2 have formula YTD(Jun)= SUM(A2:F2)
Each month, as I change Outlook numbers to actual numbers,
I need to update the YTD formula to reflect correct range,
e.g. for July, I would change G1 from JulOL to Jul and
update cell g2,g3 etc accordingly with new figures. For
cell Z2, I will then change the sum range to a2:g2 and
cell z3 with corresponding range a3:g3 (by copy/paste cell
z2)

How can I accomplish this without copying and pasting to
each z(row number) cell the new formula range but instead
automate it based on the MONTH (ie if Jan, just range a
(row no.):a(row no.); if Mar, range a(row number):c(row
number) etc)?
 
If I understand what you're asking, you could perhaps enter your outlook
(proposed budget) numbers as text.

You could then enter your Sum formula in Z2 as:

=SUM(A2:Y2)

Now, this will add your *numbers* for the entire year.

If you would enter your outlook figures preceded with an apostrophe ('),
they would *not* be added by the formula, and would *not* generate an error.
You would then only have to click in the appropriate cell and simply enter
your real numbers without the apostrophe, no format changes necessary.

Also, if you wish, you could have these *non-number* cells automatically
marked by XL (if you have a recent version), so that you can tell at a
glance which figures are not included in your Sum formula.

Go to
<Tools> <Options> <ErrorChecking> tab.
And check the "NumberStoredAsText" box.

This will automatically place a small mark in the upper left of these
*non-number* cells.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================




I have a need to update YTD sum of a range monthly.
Current setup as follows:
A B C D E F G H Z
1 Jan Feb Mar Apr May Jun JulOL AugOL ...YTDJun
2 a2 b2 c2 d2 e2 f2 g2 h2 z2
3 a3 b3 c3 d3 e3 f3 g3 h3 z3

where cell z2 have formula YTD(Jun)= SUM(A2:F2)
Each month, as I change Outlook numbers to actual numbers,
I need to update the YTD formula to reflect correct range,
e.g. for July, I would change G1 from JulOL to Jul and
update cell g2,g3 etc accordingly with new figures. For
cell Z2, I will then change the sum range to a2:g2 and
cell z3 with corresponding range a3:g3 (by copy/paste cell
z2)

How can I accomplish this without copying and pasting to
each z(row number) cell the new formula range but instead
automate it based on the MONTH (ie if Jan, just range a
(row no.):a(row no.); if Mar, range a(row number):c(row
number) etc)?
 
Sean,

Not sure I fully understand your question...but you might be looking for the
offset function.

Choose your "anchor" cell, say A2, and then do the following

For example,

=sum(offset(A2,0,0,RowsDeep, ColsWide))

So if you had June and wanted the first two rows of data till June, then
your formula becomes

=sum(offset(A2,0,0,2,6))

The "2" number is likely fixed? So you just need to input the month number.

Again, not sure if I understood your message.

Hope this helps.

Regards,
Kevin
 
Back
Top