Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I freeze 3 different columns?
I have this
columns =sum(b3+c3+d3)/3 total is in h3 1st I would like to add a new daily number in b3. I would like the numbers in c3 & c4 to shift right on enter with c4 actually deleting or dropping off. The new total would be b3-d3 2nd I have the same situation "on the same sheet"! I have =sum(e3+f3+g3)/3 total is in I3 I'd like to add a new daily number in e3 and have f3 & g3 shift right with the old g3 number deleting or going away What I have is, 2 sets of 3 columns that add then average, on the same sheet. I would like to enter a new daily number on the left of each set, clearing the 3rrd number on each. OOO..Kay.. Is this possible... Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I freeze 3 different columns?
Can you be more detailed on what's going to happen where for say 3 or 4
changes in B3? Don't need to talke about E3 or I3 since we'll assume same thing happens to those as is happening with first group. It looks like with the first change to B3 you not only want C3 and D3 to get shifted right, but you want a change in the formula in H3 from =Sum(b3+C3+D3) to =B3-D3 or perhaps =(B3-D3)/3 ? need to see series starting with the basics and then going through a couple of changes in b3 - showing what ends up in B3, C3 and D3 and and what the formula in H3 should look like at each stage. I'm not sure but I think you referred to c4 in first description when you meant D3?? "Mark" wrote: I have this columns =sum(b3+c3+d3)/3 total is in h3 1st I would like to add a new daily number in b3. I would like the numbers in c3 & c4 to shift right on enter with c4 actually deleting or dropping off. The new total would be b3-d3 2nd I have the same situation "on the same sheet"! I have =sum(e3+f3+g3)/3 total is in I3 I'd like to add a new daily number in e3 and have f3 & g3 shift right with the old g3 number deleting or going away What I have is, 2 sets of 3 columns that add then average, on the same sheet. I would like to enter a new daily number on the left of each set, clearing the 3rrd number on each. OOO..Kay.. Is this possible... Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I freeze 3 different columns?
Even my daughter caught my error.. Thank you kindly.. Yes D3
The formula in E4 is =SUM(B3+C3+D3)/3 It works fine. I need to enter a new number in B3, I need the Old B3 and C3 to shift right, to C3 and D3, (The old D3 deletes). The formula in E4 should freeze and remain in place. I will then enter a new number in B3. Basically I am tracking the last 3 days numbers. I need to enter a daily number and have the Oldest number (D3)drop. Just want to enter the newest # and have the oldest drop. Need a shift and drop somewhere. Yes the second sheet is the same. I use the same system. I would like to have both sheets 1 and 2 on sheet 1 side by side or merged . I can't seem to get double freezes. Make any sense? Thanks (VERY MUCH) Mark (Microsoft Software Reviewer) "Home Server" "Mark" wrote: I have this columns =sum(b3+c3+d3)/3 total is in h3 1st I would like to add a new daily number in b3. I would like the numbers in c3 & c4 to shift right on enter with c4 actually deleting or dropping off. The new total would be b3-d3 2nd I have the same situation "on the same sheet"! I have =sum(e3+f3+g3)/3 total is in I3 I'd like to add a new daily number in e3 and have f3 & g3 shift right with the old g3 number deleting or going away What I have is, 2 sets of 3 columns that add then average, on the same sheet. I would like to enter a new daily number on the left of each set, clearing the 3rrd number on each. OOO..Kay.. Is this possible... Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I freeze 3 different columns?
The way you've got things laid out it actually is a somewhat complex problem.
The basic problem is that once you enter a new number into B3, the old number is forgotten! Now we can set up code to check when you first click or choose B3 and to remember the number that's in there at the moment and then check again if you change that value to see if there's a new value and if so, do the data moves. We won't actually be inserting any new cells or columns, just moving information between B3, C3 and D3. E3's formula will continue to use those 3 values for its calculations. A couple of other questions before getting deeper into this - #1 - would it be possible to choose another cell (now unused) to put the new daily entry into? We could then look for a change in it and get verification from you that it needs to replace the value in B3 (and move B3 to C3 and C3 to D3 at the same time). Much easier and safer. #2 - if #1 is not a viable solution, then is B3 the only cell where you're entering new daily value? "Mark" wrote: Even my daughter caught my error.. Thank you kindly.. Yes D3 The formula in E4 is =SUM(B3+C3+D3)/3 It works fine. I need to enter a new number in B3, I need the Old B3 and C3 to shift right, to C3 and D3, (The old D3 deletes). The formula in E4 should freeze and remain in place. I will then enter a new number in B3. Basically I am tracking the last 3 days numbers. I need to enter a daily number and have the Oldest number (D3)drop. Just want to enter the newest # and have the oldest drop. Need a shift and drop somewhere. Yes the second sheet is the same. I use the same system. I would like to have both sheets 1 and 2 on sheet 1 side by side or merged . I can't seem to get double freezes. Make any sense? Thanks (VERY MUCH) Mark (Microsoft Software Reviewer) "Home Server" "Mark" wrote: I have this columns =sum(b3+c3+d3)/3 total is in h3 1st I would like to add a new daily number in b3. I would like the numbers in c3 & c4 to shift right on enter with c4 actually deleting or dropping off. The new total would be b3-d3 2nd I have the same situation "on the same sheet"! I have =sum(e3+f3+g3)/3 total is in I3 I'd like to add a new daily number in e3 and have f3 & g3 shift right with the old g3 number deleting or going away What I have is, 2 sets of 3 columns that add then average, on the same sheet. I would like to enter a new daily number on the left of each set, clearing the 3rrd number on each. OOO..Kay.. Is this possible... Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I freeze 3 different columns?
Yes Sir!
I could certainly set a new column at B3 (open) and move my 3 daily columns to c3-d3-e3. I would then, place a new number in b3 and follow your guidance. You have me quite curious now, as I have not seen very much on this. Thank you gain, Mark "JLatham" wrote: The way you've got things laid out it actually is a somewhat complex problem. The basic problem is that once you enter a new number into B3, the old number is forgotten! Now we can set up code to check when you first click or choose B3 and to remember the number that's in there at the moment and then check again if you change that value to see if there's a new value and if so, do the data moves. We won't actually be inserting any new cells or columns, just moving information between B3, C3 and D3. E3's formula will continue to use those 3 values for its calculations. A couple of other questions before getting deeper into this - #1 - would it be possible to choose another cell (now unused) to put the new daily entry into? We could then look for a change in it and get verification from you that it needs to replace the value in B3 (and move B3 to C3 and C3 to D3 at the same time). Much easier and safer. #2 - if #1 is not a viable solution, then is B3 the only cell where you're entering new daily value? "Mark" wrote: Even my daughter caught my error.. Thank you kindly.. Yes D3 The formula in E4 is =SUM(B3+C3+D3)/3 It works fine. I need to enter a new number in B3, I need the Old B3 and C3 to shift right, to C3 and D3, (The old D3 deletes). The formula in E4 should freeze and remain in place. I will then enter a new number in B3. Basically I am tracking the last 3 days numbers. I need to enter a daily number and have the Oldest number (D3)drop. Just want to enter the newest # and have the oldest drop. Need a shift and drop somewhere. Yes the second sheet is the same. I use the same system. I would like to have both sheets 1 and 2 on sheet 1 side by side or merged . I can't seem to get double freezes. Make any sense? Thanks (VERY MUCH) Mark (Microsoft Software Reviewer) "Home Server" "Mark" wrote: I have this columns =sum(b3+c3+d3)/3 total is in h3 1st I would like to add a new daily number in b3. I would like the numbers in c3 & c4 to shift right on enter with c4 actually deleting or dropping off. The new total would be b3-d3 2nd I have the same situation "on the same sheet"! I have =sum(e3+f3+g3)/3 total is in I3 I'd like to add a new daily number in e3 and have f3 & g3 shift right with the old g3 number deleting or going away What I have is, 2 sets of 3 columns that add then average, on the same sheet. I would like to enter a new daily number on the left of each set, clearing the 3rrd number on each. OOO..Kay.. Is this possible... Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I freeze 3 different columns?
Also curious as to what happens to the old E3 the last of the 3 entries, once the new number has been entered into b3, and they shift right? Thanks Mark "JLatham" wrote: The way you've got things laid out it actually is a somewhat complex problem. The basic problem is that once you enter a new number into B3, the old number is forgotten! Now we can set up code to check when you first click or choose B3 and to remember the number that's in there at the moment and then check again if you change that value to see if there's a new value and if so, do the data moves. We won't actually be inserting any new cells or columns, just moving information between B3, C3 and D3. E3's formula will continue to use those 3 values for its calculations. A couple of other questions before getting deeper into this - #1 - would it be possible to choose another cell (now unused) to put the new daily entry into? We could then look for a change in it and get verification from you that it needs to replace the value in B3 (and move B3 to C3 and C3 to D3 at the same time). Much easier and safer. #2 - if #1 is not a viable solution, then is B3 the only cell where you're entering new daily value? "Mark" wrote: Even my daughter caught my error.. Thank you kindly.. Yes D3 The formula in E4 is =SUM(B3+C3+D3)/3 It works fine. I need to enter a new number in B3, I need the Old B3 and C3 to shift right, to C3 and D3, (The old D3 deletes). The formula in E4 should freeze and remain in place. I will then enter a new number in B3. Basically I am tracking the last 3 days numbers. I need to enter a daily number and have the Oldest number (D3)drop. Just want to enter the newest # and have the oldest drop. Need a shift and drop somewhere. Yes the second sheet is the same. I use the same system. I would like to have both sheets 1 and 2 on sheet 1 side by side or merged . I can't seem to get double freezes. Make any sense? Thanks (VERY MUCH) Mark (Microsoft Software Reviewer) "Home Server" "Mark" wrote: I have this columns =sum(b3+c3+d3)/3 total is in h3 1st I would like to add a new daily number in b3. I would like the numbers in c3 & c4 to shift right on enter with c4 actually deleting or dropping off. The new total would be b3-d3 2nd I have the same situation "on the same sheet"! I have =sum(e3+f3+g3)/3 total is in I3 I'd like to add a new daily number in e3 and have f3 & g3 shift right with the old g3 number deleting or going away What I have is, 2 sets of 3 columns that add then average, on the same sheet. I would like to enter a new daily number on the left of each set, clearing the 3rrd number on each. OOO..Kay.. Is this possible... Thanks |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I freeze 3 different columns?
Mark, I'll put something together this evening based on earlier post of yours
that we can put in a new column B so we can use B3 as a 'new entry' location. As for what would be in E3 - it basically gets sent to the bit bucket. The code is actually going to work something like this: you type something into B3 and hit [enter] or move on out of that cell - we ask "Hey, Mark, you want to use this as the new daily entry?" and you click [yes] button, then the code does this: Takes whatever is in D3 and writes/copies it to E3 (E3 goes bye-bye to bit bucket) Take whatever is in C3 and writes/copies it into D3 Take your new entry in B3 and write/copy it into C3 Erase the entry in B3 to be ready for tomorrow. In the meantime, the formula, now over in F3, doesn't know or care about anything other than that it needs to recalculate itself because stuff changed in C3, D3 and E3. "Mark" wrote: Also curious as to what happens to the old E3 the last of the 3 entries, once the new number has been entered into b3, and they shift right? Thanks Mark "JLatham" wrote: The way you've got things laid out it actually is a somewhat complex problem. The basic problem is that once you enter a new number into B3, the old number is forgotten! Now we can set up code to check when you first click or choose B3 and to remember the number that's in there at the moment and then check again if you change that value to see if there's a new value and if so, do the data moves. We won't actually be inserting any new cells or columns, just moving information between B3, C3 and D3. E3's formula will continue to use those 3 values for its calculations. A couple of other questions before getting deeper into this - #1 - would it be possible to choose another cell (now unused) to put the new daily entry into? We could then look for a change in it and get verification from you that it needs to replace the value in B3 (and move B3 to C3 and C3 to D3 at the same time). Much easier and safer. #2 - if #1 is not a viable solution, then is B3 the only cell where you're entering new daily value? "Mark" wrote: Even my daughter caught my error.. Thank you kindly.. Yes D3 The formula in E4 is =SUM(B3+C3+D3)/3 It works fine. I need to enter a new number in B3, I need the Old B3 and C3 to shift right, to C3 and D3, (The old D3 deletes). The formula in E4 should freeze and remain in place. I will then enter a new number in B3. Basically I am tracking the last 3 days numbers. I need to enter a daily number and have the Oldest number (D3)drop. Just want to enter the newest # and have the oldest drop. Need a shift and drop somewhere. Yes the second sheet is the same. I use the same system. I would like to have both sheets 1 and 2 on sheet 1 side by side or merged . I can't seem to get double freezes. Make any sense? Thanks (VERY MUCH) Mark (Microsoft Software Reviewer) "Home Server" "Mark" wrote: I have this columns =sum(b3+c3+d3)/3 total is in h3 1st I would like to add a new daily number in b3. I would like the numbers in c3 & c4 to shift right on enter with c4 actually deleting or dropping off. The new total would be b3-d3 2nd I have the same situation "on the same sheet"! I have =sum(e3+f3+g3)/3 total is in I3 I'd like to add a new daily number in e3 and have f3 & g3 shift right with the old g3 number deleting or going away What I have is, 2 sets of 3 columns that add then average, on the same sheet. I would like to enter a new daily number on the left of each set, clearing the 3rrd number on each. OOO..Kay.. Is this possible... Thanks |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I freeze 3 different columns?
Mark,
Here it is! Set up for you to type new daily entry into B3, and when you give it the 'ok' it'll move that into C3 after it first moves D3 into E3 and current B3 into C3. Before showing the code, if you want, you can change your formula in F3 to this: =AVERAGE(C3:E3) That automatically adds all numbers in the range and divides them by the number of numbers. May want to play with that a bit, if cells are empty, it doesn't count them, so AVERAGE(C3:E3) with C3=1 and D3 and E3 empty (not zero, but empty) the AVERAGE is 1, but with numbers in all 3 cells it gives average of them all. With your formula, it would have been 0.33333 (1+0+0)/3. Either way, your choice. Now - how to get the code into your workbook so it functions properly. Best to do this one time on a copy of your workbook, just in case. Open the workbook, choose the page with that formula and where you want to enter information into B3. Right-click on the sheet's name tab and choose [View Code] from the popup list that appears. Copy the code below and paste it into the code module that appears and then simply close the VB Editor. To test it, type a number into B3 and then use your mouse, [Tab] or [Enter] to move to another cell. Enjoy! The initial test asks 3 questions: did you change the value in B3? and does B3 now have something in it, or did you hit the [Del] key (IsEmpty), or if you typed in something like "mark" instead of a number, it won't do anything. In the second test, if you hit the [NO] button, it doesn't do anything. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$B$3" Or _ IsEmpty(Target) Or _ Not IsNumeric(Target) Then Exit Sub End If If MsgBox("Use the new value " & Target & _ " as new Daily Entry?", vbYesNo + vbDefaultButton1 _ + vbInformation, "Verify Entry") < vbYes Then Target.ClearContents Exit Sub End If 'overwrite E3 with D3 Range("E3") = Range("D3") 'overwrite D3 with C3 Range("D3") = Range("C3") 'overwrite C3 with B3 Range("C3") = Target 'clear B3 for tomorrow Target.ClearContents End Sub "Mark" wrote: Also curious as to what happens to the old E3 the last of the 3 entries, once the new number has been entered into b3, and they shift right? Thanks Mark "JLatham" wrote: The way you've got things laid out it actually is a somewhat complex problem. The basic problem is that once you enter a new number into B3, the old number is forgotten! Now we can set up code to check when you first click or choose B3 and to remember the number that's in there at the moment and then check again if you change that value to see if there's a new value and if so, do the data moves. We won't actually be inserting any new cells or columns, just moving information between B3, C3 and D3. E3's formula will continue to use those 3 values for its calculations. A couple of other questions before getting deeper into this - #1 - would it be possible to choose another cell (now unused) to put the new daily entry into? We could then look for a change in it and get verification from you that it needs to replace the value in B3 (and move B3 to C3 and C3 to D3 at the same time). Much easier and safer. #2 - if #1 is not a viable solution, then is B3 the only cell where you're entering new daily value? "Mark" wrote: Even my daughter caught my error.. Thank you kindly.. Yes D3 The formula in E4 is =SUM(B3+C3+D3)/3 It works fine. I need to enter a new number in B3, I need the Old B3 and C3 to shift right, to C3 and D3, (The old D3 deletes). The formula in E4 should freeze and remain in place. I will then enter a new number in B3. Basically I am tracking the last 3 days numbers. I need to enter a daily number and have the Oldest number (D3)drop. Just want to enter the newest # and have the oldest drop. Need a shift and drop somewhere. Yes the second sheet is the same. I use the same system. I would like to have both sheets 1 and 2 on sheet 1 side by side or merged . I can't seem to get double freezes. Make any sense? Thanks (VERY MUCH) Mark (Microsoft Software Reviewer) "Home Server" "Mark" wrote: I have this columns =sum(b3+c3+d3)/3 total is in h3 1st I would like to add a new daily number in b3. I would like the numbers in c3 & c4 to shift right on enter with c4 actually deleting or dropping off. The new total would be b3-d3 2nd I have the same situation "on the same sheet"! I have =sum(e3+f3+g3)/3 total is in I3 I'd like to add a new daily number in e3 and have f3 & g3 shift right with the old g3 number deleting or going away What I have is, 2 sets of 3 columns that add then average, on the same sheet. I would like to enter a new daily number on the left of each set, clearing the 3rrd number on each. OOO..Kay.. Is this possible... Thanks |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I freeze 3 different columns?
Thats just perfect. Many many years ago, I did something like this with
another spreadsheet program. I have lost it now, and spend most of my time looking and reviewing. If you can make that happen, maybe, I can assist others with a topic like this. Very, very, greatly appreciated. Mark "JLatham" wrote: Mark, I'll put something together this evening based on earlier post of yours that we can put in a new column B so we can use B3 as a 'new entry' location. As for what would be in E3 - it basically gets sent to the bit bucket. The code is actually going to work something like this: you type something into B3 and hit [enter] or move on out of that cell - we ask "Hey, Mark, you want to use this as the new daily entry?" and you click [yes] button, then the code does this: Takes whatever is in D3 and writes/copies it to E3 (E3 goes bye-bye to bit bucket) Take whatever is in C3 and writes/copies it into D3 Take your new entry in B3 and write/copy it into C3 Erase the entry in B3 to be ready for tomorrow. In the meantime, the formula, now over in F3, doesn't know or care about anything other than that it needs to recalculate itself because stuff changed in C3, D3 and E3. "Mark" wrote: Also curious as to what happens to the old E3 the last of the 3 entries, once the new number has been entered into b3, and they shift right? Thanks Mark "JLatham" wrote: The way you've got things laid out it actually is a somewhat complex problem. The basic problem is that once you enter a new number into B3, the old number is forgotten! Now we can set up code to check when you first click or choose B3 and to remember the number that's in there at the moment and then check again if you change that value to see if there's a new value and if so, do the data moves. We won't actually be inserting any new cells or columns, just moving information between B3, C3 and D3. E3's formula will continue to use those 3 values for its calculations. A couple of other questions before getting deeper into this - #1 - would it be possible to choose another cell (now unused) to put the new daily entry into? We could then look for a change in it and get verification from you that it needs to replace the value in B3 (and move B3 to C3 and C3 to D3 at the same time). Much easier and safer. #2 - if #1 is not a viable solution, then is B3 the only cell where you're entering new daily value? "Mark" wrote: Even my daughter caught my error.. Thank you kindly.. Yes D3 The formula in E4 is =SUM(B3+C3+D3)/3 It works fine. I need to enter a new number in B3, I need the Old B3 and C3 to shift right, to C3 and D3, (The old D3 deletes). The formula in E4 should freeze and remain in place. I will then enter a new number in B3. Basically I am tracking the last 3 days numbers. I need to enter a daily number and have the Oldest number (D3)drop. Just want to enter the newest # and have the oldest drop. Need a shift and drop somewhere. Yes the second sheet is the same. I use the same system. I would like to have both sheets 1 and 2 on sheet 1 side by side or merged . I can't seem to get double freezes. Make any sense? Thanks (VERY MUCH) Mark (Microsoft Software Reviewer) "Home Server" "Mark" wrote: I have this columns =sum(b3+c3+d3)/3 total is in h3 1st I would like to add a new daily number in b3. I would like the numbers in c3 & c4 to shift right on enter with c4 actually deleting or dropping off. The new total would be b3-d3 2nd I have the same situation "on the same sheet"! I have =sum(e3+f3+g3)/3 total is in I3 I'd like to add a new daily number in e3 and have f3 & g3 shift right with the old g3 number deleting or going away What I have is, 2 sets of 3 columns that add then average, on the same sheet. I would like to enter a new daily number on the left of each set, clearing the 3rrd number on each. OOO..Kay.. Is this possible... Thanks |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I freeze 3 different columns?
Sir,
I just got home and didn't know there was a reply. I can't wait to try this. I'm so excited. How, do we ever repay you MVP's for the unbelieveable , wonderful assistance you give us dunce's. Thank you is not enough. If this works, I have a slightly bigger question. But alas...one small step at a time. Thank you, Thank you. Mark "JLatham" wrote: Mark, Here it is! Set up for you to type new daily entry into B3, and when you give it the 'ok' it'll move that into C3 after it first moves D3 into E3 and current B3 into C3. Before showing the code, if you want, you can change your formula in F3 to this: =AVERAGE(C3:E3) That automatically adds all numbers in the range and divides them by the number of numbers. May want to play with that a bit, if cells are empty, it doesn't count them, so AVERAGE(C3:E3) with C3=1 and D3 and E3 empty (not zero, but empty) the AVERAGE is 1, but with numbers in all 3 cells it gives average of them all. With your formula, it would have been 0.33333 (1+0+0)/3. Either way, your choice. Now - how to get the code into your workbook so it functions properly. Best to do this one time on a copy of your workbook, just in case. Open the workbook, choose the page with that formula and where you want to enter information into B3. Right-click on the sheet's name tab and choose [View Code] from the popup list that appears. Copy the code below and paste it into the code module that appears and then simply close the VB Editor. To test it, type a number into B3 and then use your mouse, [Tab] or [Enter] to move to another cell. Enjoy! The initial test asks 3 questions: did you change the value in B3? and does B3 now have something in it, or did you hit the [Del] key (IsEmpty), or if you typed in something like "mark" instead of a number, it won't do anything. In the second test, if you hit the [NO] button, it doesn't do anything. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$B$3" Or _ IsEmpty(Target) Or _ Not IsNumeric(Target) Then Exit Sub End If If MsgBox("Use the new value " & Target & _ " as new Daily Entry?", vbYesNo + vbDefaultButton1 _ + vbInformation, "Verify Entry") < vbYes Then Target.ClearContents Exit Sub End If 'overwrite E3 with D3 Range("E3") = Range("D3") 'overwrite D3 with C3 Range("D3") = Range("C3") 'overwrite C3 with B3 Range("C3") = Target 'clear B3 for tomorrow Target.ClearContents End Sub "Mark" wrote: Also curious as to what happens to the old E3 the last of the 3 entries, once the new number has been entered into b3, and they shift right? Thanks Mark "JLatham" wrote: The way you've got things laid out it actually is a somewhat complex problem. The basic problem is that once you enter a new number into B3, the old number is forgotten! Now we can set up code to check when you first click or choose B3 and to remember the number that's in there at the moment and then check again if you change that value to see if there's a new value and if so, do the data moves. We won't actually be inserting any new cells or columns, just moving information between B3, C3 and D3. E3's formula will continue to use those 3 values for its calculations. A couple of other questions before getting deeper into this - #1 - would it be possible to choose another cell (now unused) to put the new daily entry into? We could then look for a change in it and get verification from you that it needs to replace the value in B3 (and move B3 to C3 and C3 to D3 at the same time). Much easier and safer. #2 - if #1 is not a viable solution, then is B3 the only cell where you're entering new daily value? "Mark" wrote: Even my daughter caught my error.. Thank you kindly.. Yes D3 The formula in E4 is =SUM(B3+C3+D3)/3 It works fine. I need to enter a new number in B3, I need the Old B3 and C3 to shift right, to C3 and D3, (The old D3 deletes). The formula in E4 should freeze and remain in place. I will then enter a new number in B3. Basically I am tracking the last 3 days numbers. I need to enter a daily number and have the Oldest number (D3)drop. Just want to enter the newest # and have the oldest drop. Need a shift and drop somewhere. Yes the second sheet is the same. I use the same system. I would like to have both sheets 1 and 2 on sheet 1 side by side or merged . I can't seem to get double freezes. Make any sense? Thanks (VERY MUCH) Mark (Microsoft Software Reviewer) "Home Server" "Mark" wrote: I have this columns =sum(b3+c3+d3)/3 total is in h3 1st I would like to add a new daily number in b3. I would like the numbers in c3 & c4 to shift right on enter with c4 actually deleting or dropping off. The new total would be b3-d3 2nd I have the same situation "on the same sheet"! I have =sum(e3+f3+g3)/3 total is in I3 I'd like to add a new daily number in e3 and have f3 & g3 shift right with the old g3 number deleting or going away What I have is, 2 sets of 3 columns that add then average, on the same sheet. I would like to enter a new daily number on the left of each set, clearing the 3rrd number on each. OOO..Kay.. Is this possible... Thanks |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Can I freeze 3 different columns?
Made a new empty B column.
Put fomula in F column. =average etc. Clicked sheet one and viewed code. Copy and pasted. (in Virtual) Worked perfect for B4. Very excited.. And incredible. Now the news.!! Doesn't apply to anything below B4. Seems to apply to B3 only and not b4-b100 etc. B5 did not work. Should be a simple fix. Too simple for me. I will study and hope to hear what I did wrong. B4 line works great. Entered new # in B4. everything shifted right and averaged in F4 Thanks in advance. Mark "JLatham" wrote: Mark, Here it is! Set up for you to type new daily entry into B3, and when you give it the 'ok' it'll move that into C3 after it first moves D3 into E3 and current B3 into C3. Before showing the code, if you want, you can change your formula in F3 to this: =AVERAGE(C3:E3) That automatically adds all numbers in the range and divides them by the number of numbers. May want to play with that a bit, if cells are empty, it doesn't count them, so AVERAGE(C3:E3) with C3=1 and D3 and E3 empty (not zero, but empty) the AVERAGE is 1, but with numbers in all 3 cells it gives average of them all. With your formula, it would have been 0.33333 (1+0+0)/3. Either way, your choice. Now - how to get the code into your workbook so it functions properly. Best to do this one time on a copy of your workbook, just in case. Open the workbook, choose the page with that formula and where you want to enter information into B3. Right-click on the sheet's name tab and choose [View Code] from the popup list that appears. Copy the code below and paste it into the code module that appears and then simply close the VB Editor. To test it, type a number into B3 and then use your mouse, [Tab] or [Enter] to move to another cell. Enjoy! The initial test asks 3 questions: did you change the value in B3? and does B3 now have something in it, or did you hit the [Del] key (IsEmpty), or if you typed in something like "mark" instead of a number, it won't do anything. In the second test, if you hit the [NO] button, it doesn't do anything. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address < "$B$3" Or _ IsEmpty(Target) Or _ Not IsNumeric(Target) Then Exit Sub End If If MsgBox("Use the new value " & Target & _ " as new Daily Entry?", vbYesNo + vbDefaultButton1 _ + vbInformation, "Verify Entry") < vbYes Then Target.ClearContents Exit Sub End If 'overwrite E3 with D3 Range("E3") = Range("D3") 'overwrite D3 with C3 Range("D3") = Range("C3") 'overwrite C3 with B3 Range("C3") = Target 'clear B3 for tomorrow Target.ClearContents End Sub "Mark" wrote: Also curious as to what happens to the old E3 the last of the 3 entries, once the new number has been entered into b3, and they shift right? Thanks Mark "JLatham" wrote: The way you've got things laid out it actually is a somewhat complex problem. The basic problem is that once you enter a new number into B3, the old number is forgotten! Now we can set up code to check when you first click or choose B3 and to remember the number that's in there at the moment and then check again if you change that value to see if there's a new value and if so, do the data moves. We won't actually be inserting any new cells or columns, just moving information between B3, C3 and D3. E3's formula will continue to use those 3 values for its calculations. A couple of other questions before getting deeper into this - #1 - would it be possible to choose another cell (now unused) to put the new daily entry into? We could then look for a change in it and get verification from you that it needs to replace the value in B3 (and move B3 to C3 and C3 to D3 at the same time). Much easier and safer. #2 - if #1 is not a viable solution, then is B3 the only cell where you're entering new daily value? "Mark" wrote: Even my daughter caught my error.. Thank you kindly.. Yes D3 The formula in E4 is =SUM(B3+C3+D3)/3 It works fine. I need to enter a new number in B3, I need the Old B3 and C3 to shift right, to C3 and D3, (The old D3 deletes). The formula in E4 should freeze and remain in place. I will then enter a new number in B3. Basically I am tracking the last 3 days numbers. I need to enter a daily number and have the Oldest number (D3)drop. Just want to enter the newest # and have the oldest drop. Need a shift and drop somewhere. Yes the second sheet is the same. I use the same system. I would like to have both sheets 1 and 2 on sheet 1 side by side or merged . I can't seem to get double freezes. Make any sense? Thanks (VERY MUCH) Mark (Microsoft Software Reviewer) "Home Server" "Mark" wrote: I have this columns =sum(b3+c3+d3)/3 total is in h3 1st I would like to add a new daily number in b3. I would like the numbers in c3 & c4 to shift right on enter with c4 actually deleting or dropping off. The new total would be b3-d3 2nd I have the same situation "on the same sheet"! I have =sum(e3+f3+g3)/3 total is in I3 I'd like to add a new daily number in e3 and have f3 & g3 shift right with the old g3 number deleting or going away What I have is, 2 sets of 3 columns that add then average, on the same sheet. I would like to enter a new daily number on the left of each set, clearing the 3rrd number on each. OOO..Kay.. Is this possible... Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Freeze Multiple Rows/Columns | Excel Discussion (Misc queries) | |||
Freeze rows/columns in Excel | Excel Worksheet Functions | |||
freeze other columns when columns have been frozen already | Excel Worksheet Functions | |||
Freeze Two Columns | Excel Worksheet Functions | |||
How do I freeze the widths of columns? | Excel Discussion (Misc queries) |