[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Formula lost from cell and only value retained  Excel 2010

Posted on 2013-05-30
4
Medium Priority
?
381 Views
Last Modified: 2013-06-19
I have at least to cells, formated as currency , locked and hidden.  Each has a formula that produces a correct value.  On save and re-load the formula is gone and only the value (still the originally correct one) is in the cell.  Any ideas on what causes this and how to prevent?

cell 1 formula:  =ROUND(IF(DHSConversionRateOKed,MAX((dhsEWBudgetCapConversion),(DailyNFRate*365/12)-VLOOKUP(IF(ISBLANK(CLServiceDate),TODAY(),CLServiceDate),MedicaidMaintReq,2,TRUE)),MIN((dhsEWBudgetCapConversion),(DailyNFRate*365/12)-VLOOKUP(IF(ISBLANK(CLServiceDate),TODAY(),CLServiceDate),MedicaidMaintReq,2,TRUE))),2)

Cell 2 Formula: =ROUND(xlsProposedCLRateAdjustedforDaysAbsent,2)

Precedents are ok;  These had been control sources for form text boxes, though other cells that are control sources and had formulas did not loose their formulas
0
Comment
Question by:MJDercks
  • 2
  • 2
4 Comments
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 1485 total points
ID: 39207947
Are they still used as controlsources for textboxes on any forms? That can cause the behaviour you describe.
0
 

Assisted Solution

by:MJDercks
MJDercks earned 0 total points
ID: 39208694
The contols were locked so could not change, but guessing this might be the problem, I used the initialize event to set the values instead of being a control source,  This seems to have fixed the issue so far.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 1485 total points
ID: 39208987
As a general rule of thumb, I recommend not binding any controls to cells directly.
0
 

Author Closing Comment

by:MJDercks
ID: 39258790
I determined my solution before the expert and mine is more specific on what the problem is and a work-around.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

590 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