Software

Ask something
Discussion Locked

Auto change sum column referance cells

0 helpful
6 replies

by mspatel

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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

6 replies

by dklippert

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))

by dklippert

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

by dklippert

Just a test.

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

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

by mspatel

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

This discussion is closed to new replies.