Change a cells value with a function

  • Thread starter Thread starter Ryan H
  • Start date Start date
R

Ryan H

Here is my function contained in O74. C70 has "Water Heater" in it and
I73="2". Why doesn't N74 have FALSE in it? Also, is there a way to control
a Forms checkbox with a function?

=IF(C70="Water
Heater",IF(I73<39,N74="FALSE",N74="TRUE"),IF(I73<99,N74="FALSE",N74="TRUE"))
 
A formula can't edit the value of another cell; it just returns a value to
the cell containing the formula. Try this formula in N74:

=IF(C70="Water Heater",I73>=39,I73>=99)

I think the answer to your second question is that if the linked cell of
your checkbox contains a formula, like the one above, that returns TRUE or
FALSE, it can be used to control the checkbox - until the first time anyone
checks or unchecks the checkbox directly. Then the formula in the linked cell
will be replaced with TRUE or FALSE.

Hope this helps,

Hutch
 
Is there a way to check a checkbox or optionbutton if a cell is calculated to
True or False?
 
You could do something with event code. For example, if A1 is the linked cell
for your checkbox, and C1 has a formula that evaluates to TRUE or FALSE, you
could use a Worksheet_Change event like this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Target is the cell that changed anywhere on the sheet
'C1 has a formula that returns TRUE or FALSE
'A1 is the linked cell for a check box
Select Case Range("C1").Value
Case True:
Range("A1").Value = True
Case False:
Range("A1").Value = False
Case Else
'do nothing
End Select
End Sub

When any cell is changed on the sheet, C1 is evaluated. If it is TRUE (or
-1), A1 is set to TRUE and the checkbox is checked. If C1 evaluates to FALSE
(or 0), A1 is set to FALSE and the checkbox is unchecked. The checkbox can
still be checked & unchecked directly using the mouse.

To add event code to a worksheet, right-click on the name tab of the sheet
where you want this to work. Select "View code". The Visual Basic Editor
(VBE) is displayed. Paste the code in the big empty white window. Close the
VBE and the event code should be active for that sheet. If you are new to
macros, this link to Jon Peltier's site may be helpful:
http://peltiertech.com/WordPress/2008/03/09/how-to-use-someone-elses-macro/

Hope this helps,

Hutch
 
I was trying to avoid using macros, but I guess I'm going to have to in this
case. I have to admit. I've been writing macros for 4 years now and feel
very comfortable doing so, but I don't know much when it comes to writing
formulas. Thanks for the helps!
 
Back
Top