Can I freeze 3 different columns?

[Mark]

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

 

[JLatham]

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]

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"

 

[JLatham]

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]

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

 

[Mark]

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]

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.

 

[JLatham]

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]

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

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.

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

 

[Mark]

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

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

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

 

[Mark]

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

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

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