A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Change a cells value with a function



 
 
Thread Tools Display Modes
  #1  
Old April 23rd, 2010, 06:27 PM posted to microsoft.public.excel.worksheet.functions
Ryan H[_2_]
external usenet poster
 
Posts: 4
Default Change a cells value with a function

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(I7339,N74="FALSE",N74="TRUE"),IF(I739 9,N74="FALSE",N74="TRUE"))

--
Cheers,
Ryan
  #2  
Old April 23rd, 2010, 07:39 PM posted to microsoft.public.excel.worksheet.functions
Tom Hutchins
external usenet poster
 
Posts: 722
Default Change a cells value with a function

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

"Ryan H" wrote:

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(I7339,N74="FALSE",N74="TRUE"),IF(I739 9,N74="FALSE",N74="TRUE"))

--
Cheers,
Ryan

  #3  
Old April 23rd, 2010, 08:23 PM posted to microsoft.public.excel.worksheet.functions
Ryan H[_2_]
external usenet poster
 
Posts: 4
Default Change a cells value with a function

Is there a way to check a checkbox or optionbutton if a cell is calculated to
True or False?
--
Cheers,
Ryan


"Tom Hutchins" wrote:

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

"Ryan H" wrote:

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(I7339,N74="FALSE",N74="TRUE"),IF(I739 9,N74="FALSE",N74="TRUE"))

--
Cheers,
Ryan

  #4  
Old April 23rd, 2010, 09:48 PM posted to microsoft.public.excel.worksheet.functions
Tom Hutchins
external usenet poster
 
Posts: 722
Default Change a cells value with a function

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/200...e-elses-macro/

Hope this helps,

Hutch

"Ryan H" wrote:

Is there a way to check a checkbox or optionbutton if a cell is calculated to
True or False?
--
Cheers,
Ryan


"Tom Hutchins" wrote:

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

"Ryan H" wrote:

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(I7339,N74="FALSE",N74="TRUE"),IF(I739 9,N74="FALSE",N74="TRUE"))

--
Cheers,
Ryan

  #5  
Old April 26th, 2010, 01:31 PM posted to microsoft.public.excel.worksheet.functions
Ryan H[_2_]
external usenet poster
 
Posts: 4
Default Change a cells value with a function

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!
--
Cheers,
Ryan


"Tom Hutchins" wrote:

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/200...e-elses-macro/

Hope this helps,

Hutch

"Ryan H" wrote:

Is there a way to check a checkbox or optionbutton if a cell is calculated to
True or False?
--
Cheers,
Ryan


"Tom Hutchins" wrote:

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

"Ryan H" wrote:

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(I7339,N74="FALSE",N74="TRUE"),IF(I739 9,N74="FALSE",N74="TRUE"))

--
Cheers,
Ryan

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:03 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.