Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

lock Formula value

Posted on 1999-07-22
8
Medium Priority
?
314 Views
Last Modified: 2012-06-21
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?
0
Comment
Question by:antrat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 3

Expert Comment

by:DFI
ID: 1609910
As now()is a volatile function, the result _is_ volatile.
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.
0
 
LVL 9

Author Comment

by:antrat
ID: 1609911
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.
0
 
LVL 3

Expert Comment

by:DFI
ID: 1609912
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
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 9

Author Comment

by:antrat
ID: 1609913
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.
0
 
LVL 5

Expert Comment

by:vboukhar
ID: 1609914
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.CustomDocumentProperties("MyOpenDate")
End Function
2. In Thisworkbook.Open - this code:
Private Sub Workbook_Open()
flag = False
For Each p In ActiveWorkbook.CustomDocumentProperties
    If StrComp(p.Name, "MyOpenDate") = 0 Then
       flag = True
       Exit For
    End If
Next
If Not flag Then
   ThisWorkbook.CustomDocumentProperties.Add "MyOpenDate", False, msoPropertyTypeDate, Now
Else
   ThisWorkbook.CustomDocumentProperties("MyOpenDate") = Now
End If
End Sub

It adds custom property and stores in it time, when file was opened.
Hope it helps.

0
 
LVL 3

Accepted Solution

by:
DFI earned 100 total points
ID: 1609915
Crazy idea, but it works!!!

Use a workbook to store the "volatile" formulas you want to freeze the result.
Use another one to do your "online" calculations. In the cell you want to store the frozen reference to the volatile formula, store a reference to the formula in the first book.
Save and close the first book... the result in second book is frozen.

I'm pretty sure it's unclear, so let me explain with an example. Try it step by step.
Open a new workbook (e.g. Book1.xls). In sheet1, cell A1, type "=now()" (without the quotes)
Open a second workbook (e.g. Book2.xls). In sheet1, cell A1, type a reference to Book1-sheet1-A1 cell or type the actual formula "=[Book1.xls]Sheet1!$A$1" (without quotes)
In book2, sheet 1, cell A2, type "=now()"
Format A1 and A2 cells with "hh:mm:ss" to see the seconds.
Now, press several times F9. Both cells will show you the actual time.
Now, save and close Book1.xls. Back to Book2.xls, press F9. A1-value (the one referencing to the closed book) remains unchanged while A2 still updates.
Store values or formulas in other cells... A1 unchanged, A2 updated.
To update the value of A1, have both books open. Or create a macro to open and close Book1.xls

Sub OpenCloseBook1()
    Workbooks.Open FileName:="C:\My Documents\Book1.xls"
    Windows("Book2.xls").Activate
    Calculate
    Windows("Book1.xls").Activate
    ActiveWorkbook.Save
    ActiveWindow.Close
    Windows("Book2.xls").Activate
End Sub


Hope it's your solution!
Regards,
DFI
0
 
LVL 9

Author Comment

by:antrat
ID: 1609916
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.
0
 
LVL 5

Expert Comment

by:vboukhar
ID: 1609917
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
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question