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 said:
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
1000)+2)