General discussion

Locked

Auto change sum column referance cells

By mspatel ·
Item apr may jun jul...dec jan feb mar spentTODt
A 10 30 5 15 60(e4:h4)
B 5 10 10 10 45(e5:h5)
D =sum(e6:h6)
F =sum(e7:h7)
Total =sum(r4:r7)

when MthTotal for Item-A is sum from Apr-Jul using =sum(INDIRECT(MONTH(TODATE())) , the cells below for Item-B to F does not change automatic. How can I best do this? On Excel 2002/2000 and VB

This conversation is currently closed to new comments.

6 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by DKlippert In reply to Auto change sum column re ...

Assuming that you have labels in cells D3:P3 (Item, Apr, May etc.)
Assuming you have items named A, B, etc in cells D4:Dxx
Assuming you have data in, say cell E4:P4, E5:P5

You want the total number of A items used up to today:

=SUM(E4:OFFSET(E4,,MONTH(TODAY())-4))

(-4 is used because your data appears to start in April)

Total for B is:

=SUM(E5:OFFSET(E5,,MONTH(TODAY())-4))

Collapse -

by DKlippert In reply to

It seems that a colon triggers a happy face
it should read D4colonDxx
E4colonOFFSET
E5colonOFFSET

Collapse -

by DKlippert In reply to

Just a test.

<pre>=SUM(E4:OFFSET(E4,,MONTH(TODAY())-4))</pre>
<code>=SUM(E4:OFFSET(E4,,MONTH(TODAY())-4))</code>

Collapse -

by mspatel In reply to

Poster rated this answer.
Excellent support and saved lot of time.
CONGRATULATION!!! THANKS!!!

Collapse -

by mspatel In reply to Auto change sum column re ...

Point value changed by question poster.

Collapse -

by mspatel In reply to Auto change sum column re ...

This question was closed by the author

Back to Software Forum
6 total posts (Page 1 of 1)  

Related Forums