Solved

Formula lost from cell and only value retained  Excel 2010

Posted on 2013-05-30
4
346 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 495 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 495 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
location range 4 22
increment numbers by 10 11 30
Excel VBA - merge "specific" data from workbook in to "new" workbook. 19 23
Excel - Duplicate values in columns 4 0
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
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 demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

867 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now