antrat
asked on
lock Formula value
one problem I always seem to have with my spreadsheets is that when a particular formula is true I want that value to remain as it is regardless if the cell value it is referencing changes. an example of this is in cell a2 the formula would read =IF(a1=5,NOW(),"") works fine when a1 = 5 but every time excel calculates the time changes. or if the value of a1 changes it ="" . The only way Ive been able to overcome this is to use a circular reference eg in cell a2 the formula reads =IF(b1<>"",a2,IF(a1=5,NOW(
Not the best of solutions, is there another way?
Not the best of solutions, is there another way?
ASKER
DFI
What took you so long!
as the spreadsheet is used by users other that myself I would need to retain the formula because the users have no knowledge of Excel. Your suggestion of using paste special > values is one means that I use at present in other situations but is not suitable in this case.
Antrat.
What took you so long!
as the spreadsheet is used by users other that myself I would need to retain the formula because the users have no knowledge of Excel. Your suggestion of using paste special > values is one means that I use at present in other situations but is not suitable in this case.
Antrat.
Antrat,
What about preventing automatic calculation (tools/options ; "calculation" tab ; "manual" radio button and selecting or not "calculate before save") and pressing F9 when you want the results updated?
DFI
What about preventing automatic calculation (tools/options ; "calculation" tab ; "manual" radio button and selecting or not "calculate before save") and pressing F9 when you want the results updated?
DFI
ASKER
I wish it were that simple
Because the spreadsheet is being used 24hrs a day 7 days a week by up to 15 different users data is being entered all the time and reading back results all the time. So having them use F9 is not suitable as it would no longer be automated.
However if you are not able to answer this problem do you know if it is possible In VBA to prevent a specified range from recalculating while still allowing the rest of the work book to calculate. The reason is that another spreadsheet that I have developed has a area on one of the sheets that has 8 very long array formulas which recalculates every time new data is entered into a cell that the the array formulas reference slowing it down dramatically. I know you can specify a range, sheet ect to calculate but I can't find any info on isolating a specified range.
I'll award another 100 points on top of the 50 for a usable answer to both.
Antrat.
Because the spreadsheet is being used 24hrs a day 7 days a week by up to 15 different users data is being entered all the time and reading back results all the time. So having them use F9 is not suitable as it would no longer be automated.
However if you are not able to answer this problem do you know if it is possible In VBA to prevent a specified range from recalculating while still allowing the rest of the work book to calculate. The reason is that another spreadsheet that I have developed has a area on one of the sheets that has 8 very long array formulas which recalculates every time new data is entered into a cell that the the array formulas reference slowing it down dramatically. I know you can specify a range, sheet ect to calculate but I can't find any info on isolating a specified range.
I'll award another 100 points on top of the 50 for a usable answer to both.
Antrat.
You can do it with using of CustomDocumentProperties and VBA user-defined function:
1. In VBA editor add module and function in it:
Public Function getOTime()
Application.Volatile
getOTime = ThisWorkbook.CustomDocumen
End Function
2. In Thisworkbook.Open - this code:
Private Sub Workbook_Open()
flag = False
For Each p In ActiveWorkbook.CustomDocum
If StrComp(p.Name, "MyOpenDate") = 0 Then
flag = True
Exit For
End If
Next
If Not flag Then
ThisWorkbook.CustomDocumen
Else
ThisWorkbook.CustomDocumen
End If
End Sub
It adds custom property and stores in it time, when file was opened.
Hope it helps.
1. In VBA editor add module and function in it:
Public Function getOTime()
Application.Volatile
getOTime = ThisWorkbook.CustomDocumen
End Function
2. In Thisworkbook.Open - this code:
Private Sub Workbook_Open()
flag = False
For Each p In ActiveWorkbook.CustomDocum
If StrComp(p.Name, "MyOpenDate") = 0 Then
flag = True
Exit For
End If
Next
If Not flag Then
ThisWorkbook.CustomDocumen
Else
ThisWorkbook.CustomDocumen
End If
End Sub
It adds custom property and stores in it time, when file was opened.
Hope it helps.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
DFI
Thanks for your response, your suggestion does indeed work and I will be able to use it in some circumstances.
However it would not be possible for me to use your suggestion in most situations for a lot of reasons mainly due to the fact that I would end up having thousands of formulas refering to other workbooks when one of my aims is to reduce and simplify my spreadsheets. I was hoping that there would of been a way to lock the value by simply adding something to my formulas other than a circular reference.
Vboukhar
There seems to have been a bit of a missunderstanding with my question ( probaly my fault ). Your function I'm sure will work but I'm not after the time or date the workbook was opened. Maybe I should not have used the NOW() function as my example.
Antrat.
Thanks for your response, your suggestion does indeed work and I will be able to use it in some circumstances.
However it would not be possible for me to use your suggestion in most situations for a lot of reasons mainly due to the fact that I would end up having thousands of formulas refering to other workbooks when one of my aims is to reduce and simplify my spreadsheets. I was hoping that there would of been a way to lock the value by simply adding something to my formulas other than a circular reference.
Vboukhar
There seems to have been a bit of a missunderstanding with my question ( probaly my fault ). Your function I'm sure will work but I'm not after the time or date the workbook was opened. Maybe I should not have used the NOW() function as my example.
Antrat.
antrat!
It was my mistake and too complicated way. I think you can use another approach - to use two user-defined function - one to set some public variable, another - to read this valiable:
(add Module and copy-paste code below)
Public MyName
Function AddMe()
AddMe = True
If IsEmpty(MyName) Then
MyName = Now ' or any your once calculated function
End If
End Function
Function getMe()
getMe = MyName
End Function
It was my mistake and too complicated way. I think you can use another approach - to use two user-defined function - one to set some public variable, another - to read this valiable:
(add Module and copy-paste code below)
Public MyName
Function AddMe()
AddMe = True
If IsEmpty(MyName) Then
MyName = Now ' or any your once calculated function
End If
End Function
Function getMe()
getMe = MyName
End Function
Do you want
1- to fix the value "forever" or
2- do you need the possibility to recalculate the "now()" value on request?
As I understood your question, it would be the first choice... So I can suggest to edit/copy the a2 cell
and edit/paste special.../values/OK it to itself.
The formula would be replaced by its actual value.
Let us know it it's what you want or if you search for a solution with the possibility to reapply the formula on request.