I'm using a calculated value as a conversion factor, but if circumstances change at random, I need to start using a fresh conversion factor. Although I can design the cell containing the factor to change as needed, all the previously entered data converted by the factor will change, too. To get around this, I need the data, when entered, to be converted by a value instead of a calculation result. Then, if the conversion factor changes in the future, data input after will convert properly while data entered in the past remains unchanged.
Practical application: When a diabetic person uses insulin to manage blood sugar, there is a number (the factor in question) called Insulin Sensitivy Factor (ISF). It represents how much glucose in the blood is reduced by 1 unit of insulin. It is established by dividing the person's weight by the total insulin administered in 1 day (TDD). Assuming the insulin prescribed for the day is neither too much nor too little, then if the person gains or loses weight, the insulin required for the day changes appropriately by the following formula: TDD = [current weight] * [ISF]
However, if the TDD (or Total Daily Dose) is consistently not enough or consistently too much over a period of a few days, then the doctor changes the dosage prescription and the ISF must be re-established.
In the spreadsheet, the [current weight] is stored in an array of daily information but the ISF is stored in a secure cell as used similar to a CONSTANT. If the ISS value gets changed, then ALL the TDD history changes too. Bad! Very Bad! So, each day, when the weight is recorded, the formula that calculates the TDD for that day must somehow convert the ISS value from the retrieved value from the ISS's formula to a numerical value as if entered from the keyboard, then applied to the TDD formula.
Can this be done without writing a macro, just by using Excel features, settings, formulas, functions, notations, and/or shortcuts?
