As long as you want the change to be on row 2, all you need for columns B and
C are these formulas:

in B2 put =B1
in C2 put =C1

then whatever you put into B1 and C1 will be echoed into B2 and C2.

Changing the date in A2 is probably easiest to do simply by typing the new
date into it.

"hsfnwa" wrote:

i want to see Feb09 225 730 (not sum YTD).
I can't figure how excel to automatic copy the changing cell TO the cell i
want it.

Again, really appreciated your help!



"JLatham" wrote:

It's very difficult to get Excel to do what I think you want now without
resorting to VBA code (a macro).

What I understand you want now is that when you enter February's data, you
want the summary to provide you with basically a Year-to-Date total. Example:

You start in January with:

A B C
1 current 100 50
2 Jan 09 100 50

Then you come along and enter February data into row 1/current as:
A B C
1 current 225 730

and you want to see
A B C
1 current 225 730
2 Feb 09 325 780

Is that correct? If it is, some relatively easy VBA code would do the
trick. Let me work it up and have it ready to post back if you tell me I now
understand what you are looking for.



"hsfnwa" wrote:


Thanks a lot for the reply.
i guess i did not explain it well.

Here is what I want:

Row 1 is data for current month. (January) $100 $50
Row 2 is YTD summary
Jan 09 $100 $50
Feb 09 (now I change the current month data to February, how to have excel
automatic formulated to Feb. under summary?)

Again, appreciated your help!


"JLatham" wrote:

This solution is based on Current being on row 1 and the 1-Jan entries being
at row 3.

In B1, enter: =INDIRECT("B" & COUNT(B3:B1000)+2)
In C1, enter: =INDIRECT("C" & COUNT(C3:C1000)+2)
In D1, enter: =INDIRECT("D" & COUNT(D3:D1000)+2)
In E1, enter: =INDIRECT("E" & COUNT(E3:E1000)+2)

The end of the range, as :B1000 or :E1000 can be changed over time as your
list grows longer.

"hsfnwa" wrote:

The basic theory is everytime when I change the datecell under current, it
will automatic copy(link) to the next row instead override the same row
Does anyone know if excel has this ability to function this?

Your reply is highly appreciated~!

Current 100 40 200 150
Summary
1-Jan 100 40 200 150