Hello, I'm Ron, using Excel 365. I need to hide a keyed-in number (cell) with a calculated number if sheet is toggled to do so.

[RWelebny]

I have 124 modules each 7 x 14; each requiting a check mark for true or false, each requiring an area value manual input, and each a dollar value manually input.
Each module does exactly the same set of calculations using t/f, area, and $. The whole thing works well, providing gross values for other analyses.

The calculations of all the modules in use (doesn't matter how many) calculates and derives a dollar value that needs to be subtracted from the dollar value that the user first put in to the module's cell. This is to say that there are two conditions that can exist and I need to clearly differentiate whether one-or-the-other condition is in play. I have a toggle that instantly gives me the needed values, back-and-forth, so the math is done. I also have it so that the number that needs to be subtracted shows up clearly where it is needed when that condition is toggled (and goes away when it isn't)..

I have gone so far as to post nearby, the number that should replace the number ($) first put in by the user. HOW DO I COVER THE $ FIGURE FIRST KEYED BY THE USER, WITH THE DERIVED REPLACEMENT THAT IS SITTING NEARBY - WHEN I TOGGLE FOR THAT MODE? Can I make it go away if I toggle the other way?

This is easy to do with rearranging or reformatting, but the value of the work is the clarity that it presents the user. Any change interrupts the logical flow and damages the value of the work.
With thanks,

RON

 

[dat11]

To achieve the desired functionality in your spreadsheet, you can use a combination of formulas and conditional formatting to automatically replace the user's input with the derived value when toggled.

Here's a step-by-step guide:

1. Toggle Setup: Use a cell (e.g., A1) as a toggle (1 for original input, 0 for derived value).
2. Data Entry: Assume user input cells are in Column B for area and Column C for initial dollar values.
3. Derived Value Calculation: Assume derived dollar values are calculated in Column D.
4. Conditional Replacement: Use a helper column (e.g., Column E) to determine which value to display based on the toggle.
   • In cell E2, enter the formula:
     excel
     Copy code
     =IF($A$1=1, C2, D2)
     Drag this formula down to cover all modules.
5. Display Replacement: To make the derived value overwrite the initial user input, you can use the IF statement directly in the display cells. For example:
   • In cell F2 (which will show the final value to the user), enter the formula:
     excel
     Copy code
     =IF($A$1=1, C2, D2)
     Drag this formula down as needed.
6. Optional Conditional Formatting: Use conditional formatting to visually indicate when the derived value is active.
   • Select Column C (initial dollar values).
   • Go to Conditional Formatting > New Rule > Use a formula to determine which cells to format.
   • Enter the formula:
     excel
     Copy code
     =$A$1=0
   • Set the formatting (e.g., change font color to gray).

This setup allows the displayed dollar value to switch between the user input and the derived value based on the toggle, maintaining the logical flow and clarity of your work

 

[RWelebny]

To achieve the desired functionality in your spreadsheet, you can use a combination of formulas and conditional formatting to automatically replace the user's input with the derived value when toggled.

Here's a step-by-step guide:

1. Toggle Setup: Use a cell (e.g., A1) as a toggle (1 for original input, 0 for derived value).
2. Data Entry: Assume user input cells are in Column B for area and Column C for initial dollar values.
3. Derived Value Calculation: Assume derived dollar values are calculated in Column D.
4. Conditional Replacement: Use a helper column (e.g., Column E) to determine which value to display based on the toggle.
   • In cell E2, enter the formula:
     excel
     Copy code
     =IF($A$1=1, C2, D2)
     Drag this formula down to cover all modules.
5. Display Replacement: To make the derived value overwrite the initial user input, you can use the IF statement directly in the display cells. For example:
   • In cell F2 (which will show the final value to the user), enter the formula:
     excel
     Copy code
     =IF($A$1=1, C2, D2)
     Drag this formula down as needed.
6. Optional Conditional Formatting: Use conditional formatting to visually indicate when the derived value is active.
   • Select Column C (initial dollar values).
   • Go to Conditional Formatting > New Rule > Use a formula to determine which cells to format.
   • Enter the formula:
     excel
     Copy code
     =$A$1=0
   • Set the formatting (e.g., change font color to gray).

This setup allows the displayed dollar value to switch between the user input and the derived value based on the toggle, maintaining the logical flow and clarity of your work

Hello DAT 111, see the attached work where I believe to have followed your instructions properly. My question remains; is there a way to have the derived value replace the input value in column C? I am hoping to be able to make the input cell toggle back and forth from input to derived. That said, I appreciate the work that you provided as I can alter my work if needed.
Thoughts?