"Remembering" Cell Values

[Harlan]

I don't really think this is possible, but I'm going to ask anyway:
Is it possible to create a function that "remembers" the value placed in it?
I want to use one column to change values as needed. I then want those
values placed in a column (A) on a different sheet (2). That sheet is
separated into columns based on months. The month is also a variable in the
first sheet (1). When I change the month, I need to put new values in column
A, but I want the values that I first entered to stay the same.
Does this make any sense? Is it possible?
Thanks in advance.

 

[JLatham]

SECOND TRY - system claimed it was too busy to accept my previous reply.

You can probably do this with some VBA code attached to the first sheet's
Change event. The code below assumes that:
On sheet 1, A1 holds the name of the month and entries are made in column A
below it.
On sheet 2, cells A1:L1 hold names of the 12 months spelled exactly the same
as they are/will be in A1 on sheet 1.

I recommend setting up 'sheet 2' with the 12 month names before adding the
code below to the workbook.

Open your workbook, select the 1st sheet and right-click on it's name tab
and choose [View Code] from the popup list. Copy the code below and paste it
into the code module presented to you. Change the name of "Sheet2" to
whatever it really is in your workbook. After that it should work pretty
well for you.

Private Sub Worksheet_Change(ByVal Target As Range)
'ignores all changes except those in column A
'and ignores a change in A1
'also ignores changes that affect multiple cells
'A1 is assumed to hold a Month name spelled
'exactly like they are in row 1 of Sheet2
Dim destWS As Worksheet ' will represent Sheet2
Dim destMonths As Range ' A1:L1 on Sheet2
Dim anyMonth As Range

'did change affect multiple cells (as large delete)
If Target.Cells.Count > 1 Then
Exit Sub
End If
'check if change in Column A and below row 1
If Target.Row = 1 Or Target.Column > 1 Then
Exit Sub
End If
'change took place in column A below row 1
'change sheet name as required
Set destWS = ThisWorkbook.Worksheets("Sheet2")
Set destMonths = destWS.Range("A1:L1")
For Each anyMonth In destMonths
If anyMonth = Range("A1") Then ' month on this sheet
destWS.Cells(Target.Row, anyMonth.Column) = Target.Value
Exit For
End If
Next
'housekeeping
Set anyMonth = Nothing
Set destMonths = Nothing
Set destWS = Nothing
End Sub

 

[Harlan]

My sheet is actually a lot more complicated than what I originally posted,
but I just wanted to see if something like this was possible. Would you be
willing to help me out if I actually send the workbook to you to look. I
think that is the only way for me to properly explain it.

Thanks

SECOND TRY - system claimed it was too busy to accept my previous reply.

You can probably do this with some VBA code attached to the first sheet's
Change event. The code below assumes that:
On sheet 1, A1 holds the name of the month and entries are made in column A
below it.
On sheet 2, cells A1:L1 hold names of the 12 months spelled exactly the same
as they are/will be in A1 on sheet 1.

I recommend setting up 'sheet 2' with the 12 month names before adding the
code below to the workbook.

Open your workbook, select the 1st sheet and right-click on it's name tab
and choose [View Code] from the popup list. Copy the code below and paste it
into the code module presented to you. Change the name of "Sheet2" to
whatever it really is in your workbook. After that it should work pretty
well for you.

Private Sub Worksheet_Change(ByVal Target As Range)
'ignores all changes except those in column A
'and ignores a change in A1
'also ignores changes that affect multiple cells
'A1 is assumed to hold a Month name spelled
'exactly like they are in row 1 of Sheet2
Dim destWS As Worksheet ' will represent Sheet2
Dim destMonths As Range ' A1:L1 on Sheet2
Dim anyMonth As Range

'did change affect multiple cells (as large delete)
If Target.Cells.Count > 1 Then
Exit Sub
End If
'check if change in Column A and below row 1
If Target.Row = 1 Or Target.Column > 1 Then
Exit Sub
End If
'change took place in column A below row 1
'change sheet name as required
Set destWS = ThisWorkbook.Worksheets("Sheet2")
Set destMonths = destWS.Range("A1:L1")
For Each anyMonth In destMonths
If anyMonth = Range("A1") Then ' month on this sheet
destWS.Cells(Target.Row, anyMonth.Column) = Target.Value
Exit For
End If
Next
'housekeeping
Set anyMonth = Nothing
Set destMonths = Nothing
Set destWS = Nothing
End Sub

I don't really think this is possible, but I'm going to ask anyway:
Is it possible to create a function that "remembers" the value placed in it?
I want to use one column to change values as needed. I then want those
values placed in a column (A) on a different sheet (2). That sheet is
separated into columns based on months. The month is also a variable in the
first sheet (1). When I change the month, I need to put new values in column
A, but I want the values that I first entered to stay the same.
Does this make any sense? Is it possible?
Thanks in advance.

 

[Harlan]

Ok, here's the REAL problem. Although the example does give me some good
information to start with.
I have a workbook with many different sheets and they are all linked up
right now. But I am only dealing with one or two right now, so I'll just
focus on those.

The main worksheet is called EndOfPeriod. I then have sheets corresponding
to the years 2008 - 2011. Then, I have sheets corresponding to budgets for
2009 - 2011, called 'Budget 2009', etc. After that are my lookup charts
containing the chart of accounts and the dates of the periods.

On the EndOfPeriod sheet, there are 6 sections, some that are two columns
wide and some three. These sections show the data for the current periods
numbers, the past periods numbers, the current period last year and last year
but next period. They all can change depending on the year that is entered
and the period that is entered. The last two sections are for budgeting
purposes. I have a section for the current periods budget that is entered on
the Budget sheet of the current year and the budget for next period that will
be entered by the group.
What I want to be able to do is enter the budget numbers in the future
budget section, have them automatically fill in the Budget sheet in the
correct period (whatever is entered at the top of the sheet), and then stay
on the budget sheet when I change to the next period. Is this at all
possible?? Am I crazy?? Or am I asking too much of excel??
I can email the workbook if needed.

Thanks

My sheet is actually a lot more complicated than what I originally posted,
but I just wanted to see if something like this was possible. Would you be
willing to help me out if I actually send the workbook to you to look. I
think that is the only way for me to properly explain it.

Thanks

SECOND TRY - system claimed it was too busy to accept my previous reply.

You can probably do this with some VBA code attached to the first sheet's
Change event. The code below assumes that:
On sheet 1, A1 holds the name of the month and entries are made in column A
below it.
On sheet 2, cells A1:L1 hold names of the 12 months spelled exactly the same
as they are/will be in A1 on sheet 1.

I recommend setting up 'sheet 2' with the 12 month names before adding the
code below to the workbook.

Open your workbook, select the 1st sheet and right-click on it's name tab
and choose [View Code] from the popup list. Copy the code below and paste it
into the code module presented to you. Change the name of "Sheet2" to
whatever it really is in your workbook. After that it should work pretty
well for you.

Private Sub Worksheet_Change(ByVal Target As Range)
'ignores all changes except those in column A
'and ignores a change in A1
'also ignores changes that affect multiple cells
'A1 is assumed to hold a Month name spelled
'exactly like they are in row 1 of Sheet2
Dim destWS As Worksheet ' will represent Sheet2
Dim destMonths As Range ' A1:L1 on Sheet2
Dim anyMonth As Range

'did change affect multiple cells (as large delete)
If Target.Cells.Count > 1 Then
Exit Sub
End If
'check if change in Column A and below row 1
If Target.Row = 1 Or Target.Column > 1 Then
Exit Sub
End If
'change took place in column A below row 1
'change sheet name as required
Set destWS = ThisWorkbook.Worksheets("Sheet2")
Set destMonths = destWS.Range("A1:L1")
For Each anyMonth In destMonths
If anyMonth = Range("A1") Then ' month on this sheet
destWS.Cells(Target.Row, anyMonth.Column) = Target.Value
Exit For
End If
Next
'housekeeping
Set anyMonth = Nothing
Set destMonths = Nothing
Set destWS = Nothing
End Sub

I don't really think this is possible, but I'm going to ask anyway:
Is it possible to create a function that "remembers" the value placed in it?
I want to use one column to change values as needed. I then want those
values placed in a column (A) on a different sheet (2). That sheet is
separated into columns based on months. The month is also a variable in the
first sheet (1). When I change the month, I need to put new values in column
A, but I want the values that I first entered to stay the same.
Does this make any sense? Is it possible?
Thanks in advance.

 

[JLatham]

Harlan,
It's almost always more complicated than people initially post here .
Why don't you go ahead and send me the workbook as an attachment to email and
remind me of this discussion (link to your initial post if possible, or at
least remind me you posted as Harlan here) and I'll see if I can't help with
it. I'll see your email when I return from the office on whatever day you
send it. Send to (remove spaces)
Help From @ jlathamsite.com

Ok, here's the REAL problem. Although the example does give me some good
information to start with.
I have a workbook with many different sheets and they are all linked up
right now. But I am only dealing with one or two right now, so I'll just
focus on those.

The main worksheet is called EndOfPeriod. I then have sheets corresponding
to the years 2008 - 2011. Then, I have sheets corresponding to budgets for
2009 - 2011, called 'Budget 2009', etc. After that are my lookup charts
containing the chart of accounts and the dates of the periods.

On the EndOfPeriod sheet, there are 6 sections, some that are two columns
wide and some three. These sections show the data for the current periods
numbers, the past periods numbers, the current period last year and last year
but next period. They all can change depending on the year that is entered
and the period that is entered. The last two sections are for budgeting
purposes. I have a section for the current periods budget that is entered on
the Budget sheet of the current year and the budget for next period that will
be entered by the group.
What I want to be able to do is enter the budget numbers in the future
budget section, have them automatically fill in the Budget sheet in the
correct period (whatever is entered at the top of the sheet), and then stay
on the budget sheet when I change to the next period. Is this at all
possible?? Am I crazy?? Or am I asking too much of excel??
I can email the workbook if needed.

Thanks

My sheet is actually a lot more complicated than what I originally posted,
but I just wanted to see if something like this was possible. Would you be
willing to help me out if I actually send the workbook to you to look. I
think that is the only way for me to properly explain it.

Thanks

SECOND TRY - system claimed it was too busy to accept my previous reply.

You can probably do this with some VBA code attached to the first sheet's
Change event. The code below assumes that:
On sheet 1, A1 holds the name of the month and entries are made in column A
below it.
On sheet 2, cells A1:L1 hold names of the 12 months spelled exactly the same
as they are/will be in A1 on sheet 1.

I recommend setting up 'sheet 2' with the 12 month names before adding the
code below to the workbook.

Open your workbook, select the 1st sheet and right-click on it's name tab
and choose [View Code] from the popup list. Copy the code below and paste it
into the code module presented to you. Change the name of "Sheet2" to
whatever it really is in your workbook. After that it should work pretty
well for you.

Private Sub Worksheet_Change(ByVal Target As Range)
'ignores all changes except those in column A
'and ignores a change in A1
'also ignores changes that affect multiple cells
'A1 is assumed to hold a Month name spelled
'exactly like they are in row 1 of Sheet2
Dim destWS As Worksheet ' will represent Sheet2
Dim destMonths As Range ' A1:L1 on Sheet2
Dim anyMonth As Range

'did change affect multiple cells (as large delete)
If Target.Cells.Count > 1 Then
Exit Sub
End If
'check if change in Column A and below row 1
If Target.Row = 1 Or Target.Column > 1 Then
Exit Sub
End If
'change took place in column A below row 1
'change sheet name as required
Set destWS = ThisWorkbook.Worksheets("Sheet2")
Set destMonths = destWS.Range("A1:L1")
For Each anyMonth In destMonths
If anyMonth = Range("A1") Then ' month on this sheet
destWS.Cells(Target.Row, anyMonth.Column) = Target.Value
Exit For
End If
Next
'housekeeping
Set anyMonth = Nothing
Set destMonths = Nothing
Set destWS = Nothing
End Sub


:

I don't really think this is possible, but I'm going to ask anyway:
Is it possible to create a function that "remembers" the value placed in it?
I want to use one column to change values as needed. I then want those
values placed in a column (A) on a different sheet (2). That sheet is
separated into columns based on months. The month is also a variable in the
first sheet (1). When I change the month, I need to put new values in column
A, but I want the values that I first entered to stay the same.
Does this make any sense? Is it possible?
Thanks in advance.