# Software

## General discussion

Locked

### Auto change sum column referance cells

By ·
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.

Thread display: Collapse - | Expand +

Collapse -
by 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 -

It seems that a colon triggers a happy face
E4colonOFFSET
E5colonOFFSET

Collapse -

Just a test.

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

Collapse -

Excellent support and saved lot of time.
CONGRATULATION!!! THANKS!!!

Collapse -
by In reply to Auto change sum column re ...

Point value changed by question poster.

Collapse -
by In reply to Auto change sum column re ...

This question was closed by the author

## Related Discussions

• 2

zillah2004 ·

• 3

#### Neat scanner NWDB files, how can you access the images?

joseppiedotcom99 ·

• 4

#### Quickbooks 2017 crash com Error

AcerNancy98 ·

• 1

#### php related validation issue

himanitechbag ·

• 2

WernDB ·