How do store calculated Value as Static entry

Microsoft Excel 2010
September 22, 2010 at 03:56:14
Specs: Windows 7
In Excel 2010 I have a cell that looks-up a calculated valu based on the values in 3 other cells.
I want that to be store as static so that when the date changes it does not change.
The code I have at the moment calculates the value correctly but when I change the date It recalculates

=COUNTIF(Stage!$H$2:$H$3000,B6&$D$1&$E$1)

Stage!$H$2:$H$3000 contains a calculated entry based on 3 cells which are =$I$1&$E2&$F2
$I$1 =TODAY() $E2 is a Text Value and $F2 is a text value

B6 contains a date $D$1 abd $E$1 contain text values

What happens is the countif counts occurances where Date and both text values are the same and puts the count in a cell.
I want this value to stay the same when the TODAY() value changes


See More: How do store calculated Value as Static entry

Report •


#1
September 22, 2010 at 04:32:23
Copy...Paste...Values

or Copy...PasteSpecial...Values

If you want it to happen automatically, you can use VBA to perform the above operations but you'd need to tell what will trigger the code to run. i.e. When do you want the data to become static?


Report •

#2
September 22, 2010 at 05:29:10
If you want it to happen automatically, you can use VBA to perform the above operations but you'd need to tell what will trigger the code to run. i.e. When do you want the data to become static?

Any Idea of code to do this as I need it to do it after cell updates?
Added complication there are multiple dates on the worksheet and I only want the one that matches by date to update and not all the others. Then once updated stay static.


Report •

#3
September 22, 2010 at 05:48:57
Hi, regarding VBA I have wtitten something like
if Value (B3:B3000) value < F1 then C2:C3000 update = false

Where:
B3 to B3000 are dates
F1 is Todays date
C2 to C3000 are the values that have the formula that once updated I want to stay static

VBA, of course throws a syntax error and Idea how to do this?
Thanks


Report •

Related Solutions

#4
September 22, 2010 at 06:50:30
You can't "turn off" the update by setting anything equal to "False".

What you need to do is either:

1 - Use VBA to replace the formula with the current value, thus losing the formula. In it's simplest form it would look like this:

Sub MakeStatic()
 Range("A1") = Range("A1").Value
End Sub

Based on your example, something like this should work:

Sub MakeStaticRng()
 For rw = 3 To 3000
   If Range("B" & rw) < Range("F1") Then
      Range("C" & rw) = Range("C" & rw).Value
   End If
 Next
End Sub

Note: I suggest you test this in a backup copy of your workbook, since you are going to lose the formulas and Macros cannot be undone.

If you don't want to lose the formula, then you would need VBA to put the formula back in the cell based on some other criteria.

Bottom line is that you can't have a formula that's dependent on a volatile function like TODAY() and a static value in a cell at the same time.

If you use this code in a Worksheet_Change event, it will fire automatically when your worksheet changes. You can control when it does what it is does by having the code test the address of the changed cell so that it doesn't fire unnecessarily. If that is of interest to you, you'll need to tell us what change(s) should trigger the code.

Or...

2 - Eliminate the formula completely and use VBA to do the entire calculation and place the results in the cells. That's a bit more complicated, but it could be done.


Report •

#5
September 22, 2010 at 08:19:44
Perfect!
VBA worked exactly the way I needed
Thanks

Report •

#6
September 22, 2010 at 10:31:15
Anytime.

Report •

#7
September 23, 2010 at 04:54:04
Hi, sorry, you mentioned about setting a trigger to fire the event?
How do I get it to fire when date changes (which is F1 = TODAY()? It woked yesterday but did not fire when I opened it today.
At the moment it calculates the value for the new date but does not make the earlier dates static

This is the code, so far

Private Sub Worksheet_Change(ByVal Target As Range)
For rw = 3 To 3000
If Range("B" & rw) < Range("F1") Then
Range("C" & rw) = Range("C" & rw).Value
End If
Next
End Sub


Report •

#8
September 23, 2010 at 06:59:40
Let's introduce the concept of "volitile functions".

The following was stolen without permission from:

http://msdn.microsoft.com/en-us/lib...

Volatile and Non-Volatile Functions

Excel supports the concept of a volatile function, that is, one whose value cannot be assumed to be the same from one moment to the next even if none of its arguments (if it takes any) has changed. Excel reevaluates cells that contain volatile functions, together with all dependents, every time that it recalculates. For this reason, too much reliance on volatile functions can make recalculation times slow. Use them sparingly.

Since TODAY() is a volitile function, it is going to recalculate every time the sheet opens, closes, recalcualtes, etc. Try this: Create a new workbook with nothing but an =TODAY() function in a cell. Save it, open it and try to close it. Excel will ask you if you want to Save the Changes, even though you didn't change anything.

However, volitile functions do not trigger change event macros, which is why you are not seeing code run.

(A side note...I need to come up with a new phrase to replace "fire the event". In reality, the Change event "fires" with every change a user makes to the worksheet, but in many (most?) cases the first line checks to see what the change was and either runs the rest of the code or doesn't. For example, this code will "fire" whenever the worksheet changes, but it will only execute the loop section if the change was made to A1. Note the "If Target.Address" instruction.

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$A$1" then
  For rw = 3 To 3000
    If Range("B" & rw) < Range("F1") Then
       Range("C" & rw) = Range("C" & rw).Value
    End If
  Next
 End If
End Sub

End side note...)

So, how do we get the code to run when the workbook opens?

How about using the Open event instead?

- Right click the sheet tab and delete the Change event code.

- In the VBA editor, assuming you have the Project Explorer pane showing, (View...Project Explorer) you should see a listing for ThisWorkbook.

- Double Click ThisWorkbook and a new editor pane will open.
- Click the Drop Down next to General and choose Workbook.

The framework for a Workbook_Open macro should appear.

Highlight the text and paste the code below in it's place. This code will run everytime the workbook is opened. I tested it by changing my system date to "tomorrow" (11/24) and then opening the workbook and it changed all the cells next to today's date (11/23) or earlier.

Private Sub Workbook_Open()
 For rw = 3 To 3000
  If Sheets(1).Range("B" & rw) < Sheets(1).Range("F1") Then
   Sheets(1).Range("C" & rw) = Sheets(1).Range("C" & rw).Value
  End If
 Next
End Sub

One more note:

The code is going to check the date in each cell in the range B1:B3000 and execute the .Value line regardless of whether there is a formula in the cell or not. In other words, it's going to "change" cells in Column C that it has previously changed, but since there is already a Value in the cell, nothing will happen.

The only way to prevent this "change" is to check for the date and then check for a formula. In terms of efficiency, I'm not sure that there is much difference between checking for a formula before making a change and just executing the change line even if it's not needed.


Report •

#9
September 23, 2010 at 07:49:51
Hi, We (you) are getting close.

I had to change the code a bit (changed the < F1 to <= F1 ) and fired it before save
. I also cut the amout of cells it updates to 200 as I don't think I need more and it was taking a bit of time to update 3000 cells, my fault.
This seems to have done the trick.
Thnks again for your help.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
For rw = 3 To 200
If Sheets(7).Range("B" & rw) <= Sheets(7).Range("F1") Then
Sheets(7).Range("C" & rw) = Sheets(7).Range("C" & rw).Value
End If
Next
End Sub


Report •

#10
September 23, 2010 at 10:30:27
As opposed to hard coding the 200 you can let VBA find the last row that contains data in Column B as follows:


 Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
   last_cell = Sheets(1).Range("B" & Rows.Count).End(xlup).Row
     for rw = 3 to last_cell
      etc.


Report •

Ask Question