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

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

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

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

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

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

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

I would like to know if excel could automatic destined the current month data
to the appropriate row accordingly.
As your above suggestion, I could do the =B1 C1,,, for Feb. but when I
change the current month(next month)data, it will override whats on Feb, but
not post to next row. I will study the VBA code you mentioned to me. Not too
familiar with that.





"JLatham" wrote:

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

OK, having gone down that other path, we'll try a VBA code solution. This is
not a fully automatic thing and I'll explain why not. We could use VBA code
to add a new entry at the bottom of the summary list columns each time you
changed a value in row 1 (current). But since you might just be correcting
an error entry, that is not a good way to do it - although it might be a
great way to get really long lists that you have to then clean up manually.
So, this is a semi-automatic way: once you have entered the new values on
row 1, you would run this routine to copy them to the bottom of the summary
list.

You can call the code a couple of ways:
Without doing anything else other than putting the code into the workbook,
you can use Tools -- Macro -- Macros to select the CopyCurrent macro and
click the [Run] button to get the job done, or
You might add a text box from the Drawing toolbar and assign the CopyCurrent
to it as the macro to run when you click on it. How: draw the text box and
then right-click on the edge of it and choose Assign Macro from the list that
appears -- of course you need to put the code into the workbook before doing
that.

How to put the code into your workbook:
Open the workbook and press [Alt]+[F11] to open the VB Editor (VBE). In the
VBE use its menu to Insert -- Module. Copy the code below and paste it into
the empty module presented to you. Change any of the values assigned to the
Const declarations, such as the name of the worksheet, what rows are
involved, and the first and last columns that will have numbers to be copied.
Once you do that, it should run properly for you. The first time you use
it, if there are no entries in the summary area, it will ask you for a date,
after that it will just use the next month.

Here's the code, hope it helps.

Sub CopyCurrent()
'change these Const values to
'match the way your sheet is
'laid out
Const mySheetName = "Sheet1"
Const dateColumn = "A"
Const currentRow = 1
Const firstMonthRow = 3
Const firstColToCopy = "B"
Const lastColToCopy = "D"
'end of user defined Constants
Dim mySheet As Worksheet
Dim lastRow As Long
Dim whatDate As Date
Dim LC As Integer

Set mySheet = ThisWorkbook.Worksheets(mySheetName)
lastRow = mySheet.Range(dateColumn & _
Rows.Count).End(xlUp).Row + 1
If lastRow < firstMonthRow Then
lastRow = firstMonthRow
End If
If lastRow firstMonthRow Then
whatDate = mySheet.Cells(lastRow - 1, _
Range(dateColumn & "1").Column)
Else
whatDate = InputBox("Enter new date (M/D/YY):", _
"Date", Now())
'have to subtract 1 from month
'so we can change it later
whatDate = DateSerial(Year(whatDate), _
Month(whatDate) - 1, Day(whatDate))
End If
Application.ScreenUpdating = False ' for speed
mySheet.Cells(lastRow, Range(dateColumn & "1").Column) = _
DateSerial(Year(whatDate), _
Month(whatDate) + 1, Day(whatDate))
mySheet.Cells(lastRow, _
Range(dateColumn & "1").Column).NumberFormat = "mmm-yy"

For LC = Range(firstColToCopy & "1").Column To _
Range(lastColToCopy & "1").Column
mySheet.Cells(lastRow, LC).Value = _
mySheet.Cells(currentRow, LC).Value
mySheet.Cells(lastRow, LC).NumberFormat = "$#,##0.00"
Next ' end of LC loop
Set mySheet = Nothing ' housekeeping
End Sub

"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

Hi JLatham,

The code your provided below is really helpful.
I have more one questions. if i want the destine copied cell go to other
sheet in the workbook, could you point out where i should make change in
below code? I really appreciated your help~

Thanks!


"JLatham" wrote:

OK, having gone down that other path, we'll try a VBA code solution. This is
not a fully automatic thing and I'll explain why not. We could use VBA code
to add a new entry at the bottom of the summary list columns each time you
changed a value in row 1 (current). But since you might just be correcting
an error entry, that is not a good way to do it - although it might be a
great way to get really long lists that you have to then clean up manually.
So, this is a semi-automatic way: once you have entered the new values on
row 1, you would run this routine to copy them to the bottom of the summary
list.

You can call the code a couple of ways:
Without doing anything else other than putting the code into the workbook,
you can use Tools -- Macro -- Macros to select the CopyCurrent macro and
click the [Run] button to get the job done, or
You might add a text box from the Drawing toolbar and assign the CopyCurrent
to it as the macro to run when you click on it. How: draw the text box and
then right-click on the edge of it and choose Assign Macro from the list that
appears -- of course you need to put the code into the workbook before doing
that.

How to put the code into your workbook:
Open the workbook and press [Alt]+[F11] to open the VB Editor (VBE). In the
VBE use its menu to Insert -- Module. Copy the code below and paste it into
the empty module presented to you. Change any of the values assigned to the
Const declarations, such as the name of the worksheet, what rows are
involved, and the first and last columns that will have numbers to be copied.
Once you do that, it should run properly for you. The first time you use
it, if there are no entries in the summary area, it will ask you for a date,
after that it will just use the next month.

Here's the code, hope it helps.

Sub CopyCurrent()
'change these Const values to
'match the way your sheet is
'laid out
Const mySheetName = "Sheet1"
Const dateColumn = "A"
Const currentRow = 1
Const firstMonthRow = 3
Const firstColToCopy = "B"
Const lastColToCopy = "D"
'end of user defined Constants
Dim mySheet As Worksheet
Dim lastRow As Long
Dim whatDate As Date
Dim LC As Integer

Set mySheet = ThisWorkbook.Worksheets(mySheetName)
lastRow = mySheet.Range(dateColumn & _
Rows.Count).End(xlUp).Row + 1
If lastRow < firstMonthRow Then
lastRow = firstMonthRow
End If
If lastRow firstMonthRow Then
whatDate = mySheet.Cells(lastRow - 1, _
Range(dateColumn & "1").Column)
Else
whatDate = InputBox("Enter new date (M/D/YY):", _
"Date", Now())
'have to subtract 1 from month
'so we can change it later
whatDate = DateSerial(Year(whatDate), _
Month(whatDate) - 1, Day(whatDate))
End If
Application.ScreenUpdating = False ' for speed
mySheet.Cells(lastRow, Range(dateColumn & "1").Column) = _
DateSerial(Year(whatDate), _
Month(whatDate) + 1, Day(whatDate))
mySheet.Cells(lastRow, _
Range(dateColumn & "1").Column).NumberFormat = "mmm-yy"

For LC = Range(firstColToCopy & "1").Column To _
Range(lastColToCopy & "1").Column
mySheet.Cells(lastRow, LC).Value = _
mySheet.Cells(currentRow, LC).Value
mySheet.Cells(lastRow, LC).NumberFormat = "$#,##0.00"
Next ' end of LC loop
Set mySheet = Nothing ' housekeeping
End Sub

"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

We have to change the code to deal with 2 sheets. The code below will do
that for you. sourceSheet is the one with entries in row 1, destSheet is the
sheet the values will be copied to.

Sub CopyCurrent()
'change these Const values to
'match the way your sheet is
'laid out
Const sourceSheetName = "Sheet1"
Const destSheetName = "Sheet2"
Const dateColumn = "A"
Const currentRow = 1
Const firstMonthRow = 3
Const firstColToCopy = "B"
Const lastColToCopy = "D"
'end of user defined Constants
Dim sourceSheet As Worksheet
Dim destSheet As Worksheet
Dim lastRow As Long
Dim whatDate As Date
Dim LC As Integer

Set sourceSheet = ThisWorkbook.Worksheets(sourceSheetName)
Set destSheet = ThisWorkbook.Worksheets(destSheetName)
lastRow = destSheet.Range(dateColumn & _
Rows.Count).End(xlUp).Row + 1
If lastRow < firstMonthRow Then
lastRow = firstMonthRow
End If
If lastRow firstMonthRow Then
whatDate = destSheet.Cells(lastRow - 1, _
Range(dateColumn & "1").Column)
Else
whatDate = InputBox("Enter new date (M/D/YY):", _
"Date", Now())
'have to subtract 1 from month
'so we can change it later
whatDate = DateSerial(Year(whatDate), _
Month(whatDate) - 1, Day(whatDate))
End If
Application.ScreenUpdating = False ' for speed
destSheet.Cells(lastRow, Range(dateColumn & "1").Column) = _
DateSerial(Year(whatDate), _
Month(whatDate) + 1, Day(whatDate))
destSheet.Cells(lastRow, _
Range(dateColumn & "1").Column).NumberFormat = "mmm-yy"

For LC = Range(firstColToCopy & "1").Column To _
Range(lastColToCopy & "1").Column
destSheet.Cells(lastRow, LC).Value = _
sourceSheet.Cells(currentRow, LC).Value
destSheet.Cells(lastRow, LC).NumberFormat = "$#,##0.00"
Next ' end of LC loop
Set destSheet = Nothing ' housekeeping
Set sourceSheet = Nothing
End Sub


"hsfnwa" wrote:

Hi JLatham,

The code your provided below is really helpful.
I have more one questions. if i want the destine copied cell go to other
sheet in the workbook, could you point out where i should make change in
below code? I really appreciated your help~

Thanks!


"JLatham" wrote:

OK, having gone down that other path, we'll try a VBA code solution. This is
not a fully automatic thing and I'll explain why not. We could use VBA code
to add a new entry at the bottom of the summary list columns each time you
changed a value in row 1 (current). But since you might just be correcting
an error entry, that is not a good way to do it - although it might be a
great way to get really long lists that you have to then clean up manually.
So, this is a semi-automatic way: once you have entered the new values on
row 1, you would run this routine to copy them to the bottom of the summary
list.

You can call the code a couple of ways:
Without doing anything else other than putting the code into the workbook,
you can use Tools -- Macro -- Macros to select the CopyCurrent macro and
click the [Run] button to get the job done, or
You might add a text box from the Drawing toolbar and assign the CopyCurrent
to it as the macro to run when you click on it. How: draw the text box and
then right-click on the edge of it and choose Assign Macro from the list that
appears -- of course you need to put the code into the workbook before doing
that.

How to put the code into your workbook:
Open the workbook and press [Alt]+[F11] to open the VB Editor (VBE). In the
VBE use its menu to Insert -- Module. Copy the code below and paste it into
the empty module presented to you. Change any of the values assigned to the
Const declarations, such as the name of the worksheet, what rows are
involved, and the first and last columns that will have numbers to be copied.
Once you do that, it should run properly for you. The first time you use
it, if there are no entries in the summary area, it will ask you for a date,
after that it will just use the next month.

Here's the code, hope it helps.

Sub CopyCurrent()
'change these Const values to
'match the way your sheet is
'laid out
Const mySheetName = "Sheet1"
Const dateColumn = "A"
Const currentRow = 1
Const firstMonthRow = 3
Const firstColToCopy = "B"
Const lastColToCopy = "D"
'end of user defined Constants
Dim mySheet As Worksheet
Dim lastRow As Long
Dim whatDate As Date
Dim LC As Integer

Set mySheet = ThisWorkbook.Worksheets(mySheetName)
lastRow = mySheet.Range(dateColumn & _
Rows.Count).End(xlUp).Row + 1
If lastRow < firstMonthRow Then
lastRow = firstMonthRow
End If
If lastRow firstMonthRow Then
whatDate = mySheet.Cells(lastRow - 1, _
Range(dateColumn & "1").Column)
Else
whatDate = InputBox("Enter new date (M/D/YY):", _
"Date", Now())
'have to subtract 1 from month
'so we can change it later
whatDate = DateSerial(Year(whatDate), _
Month(whatDate) - 1, Day(whatDate))
End If
Application.ScreenUpdating = False ' for speed
mySheet.Cells(lastRow, Range(dateColumn & "1").Column) = _
DateSerial(Year(whatDate), _
Month(whatDate) + 1, Day(whatDate))
mySheet.Cells(lastRow, _
Range(dateColumn & "1").Column).NumberFormat = "mmm-yy"

For LC = Range(firstColToCopy & "1").Column To _
Range(lastColToCopy & "1").Column
mySheet.Cells(lastRow, LC).Value = _
mySheet.Cells(currentRow, LC).Value
mySheet.Cells(lastRow, LC).NumberFormat = "$#,##0.00"
Next ' end of LC loop
Set mySheet = Nothing ' housekeeping
End Sub

"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