Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"Remembering" Cell Values
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"Remembering" Cell Values
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" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"Remembering" Cell Values
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 "JLatham" wrote: 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" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"Remembering" Cell Values
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 "Harlan" wrote: 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 "JLatham" wrote: 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" wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
"Remembering" Cell Values
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 "Harlan" wrote: 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 "Harlan" wrote: 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 "JLatham" wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
excel displays "l" instead of "‚¬" symbol for Euro values | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
How to create a scatter chart with 2 "X" values with common "Y"s | Charts and Charting in Excel | |||
Complex if test program possible? If "value" "value", paste "value" in another cell? | Excel Discussion (Misc queries) | |||
Changing "returned" values from "0" to "blank" | Excel Worksheet Functions |