Directing the output of one cell into another cell

  • Thread starter Thread starter Confused
  • Start date Start date
C

Confused

I have an IF statement that works exactly the way I want and results in a
TRUE/FALSE. The problem is, I want that true/false result to be directed to
another cell without having to make that cell have a formula in it like =A13
(if A13 is where I have the IF formula). Essentially, I want the target cell
to be able to accept data from a couple of sources, all being true/false
tests, and not have a formula in it. Does that make sense? Something like:
IF B21=TRUE then A13=TRUE, which is easy in VBA, but seems I can't get Excel
to do the same.

Thanks
Conf.
 
Confused said:
I have an IF statement that works exactly the way I want and results in a
TRUE/FALSE. The problem is, I want that true/false result to be directed to
another cell without having to make that cell have a formula in it like =A13
Click to expand...
....

Then your only option is using Change or Calculate event handler
macros written in VBA.

A cell's value can ONLY change automatically if it contains a formula
or if event handlers triggered either by entries in other cells or by
recalculation change the cell's contents.
 
Something has to tell A13 to show the same value as B21. Normally this would
require entering the formula =B21 in A13.

Aaron Blood has a sample spreadsheet on his site that may help you work
around your multiple True False problem.
http://www.xl-logic.com

Search for the file "Binary Switch Logic" in the downloads area. You will
need to register.
 
Hi,

Here is some sample code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("B21"))
If Not isect Is Nothing Then
IF Target = TRUE Then
[A13]=True
End If
End If
End Sub
 
AHA! Excellent, thank you very much, that works perfectly.

Conf.

Shane Devenshire said:
Hi,

Here is some sample code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("B21"))
If Not isect Is Nothing Then
IF Target = TRUE Then
[A13]=True
End If
End If
End Sub
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


Confused said:
I have an IF statement that works exactly the way I want and results in a
TRUE/FALSE. The problem is, I want that true/false result to be directed
to
another cell without having to make that cell have a formula in it like
=A13
(if A13 is where I have the IF formula). Essentially, I want the target
cell
to be able to accept data from a couple of sources, all being true/false
tests, and not have a formula in it. Does that make sense? Something
like:
IF B21=TRUE then A13=TRUE, which is easy in VBA, but seems I can't get
Excel
to do the same.

Thanks
Conf.
Click to expand...
Click to expand...
 
Well, bummer, I thought I had it working, then I went and changed something
and now it isn't. Here's the code I'm using:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("B202"))
If Not isect Is Nothing Then
If Target = True Then
[B201] = True
End If
End If
End Sub


In cell B202 I have this formula: =IF($E$12=3,TRUE,FALSE) now, when E12=3,
that cell becomes TRUE, but nothing happens in B201? Do I have it right?
I've also tried redirecting it to a completely new cell, C202, by changing
this line:

[B201] = True

so that it points to C202, which has never been used:

[C202] = True

and nothing happens there either, does the target cell need to have
something in it to accept the new value?

Thanks again.

Conf.

Shane Devenshire said:
Hi,

Here is some sample code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("B21"))
If Not isect Is Nothing Then
IF Target = TRUE Then
[A13]=True
End If
End If
End Sub
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


Confused said:
I have an IF statement that works exactly the way I want and results in a
TRUE/FALSE. The problem is, I want that true/false result to be directed
to
another cell without having to make that cell have a formula in it like
=A13
(if A13 is where I have the IF formula). Essentially, I want the target
cell
to be able to accept data from a couple of sources, all being true/false
tests, and not have a formula in it. Does that make sense? Something
like:
IF B21=TRUE then A13=TRUE, which is easy in VBA, but seems I can't get
Excel
to do the same.

Thanks
Conf.
Click to expand...
Click to expand...
 
Your cell's value changes as the result of a formula.

The worksheet_Change event fires when someone makes a change manually (mouse or
keyboard). So this _change event isn't firing.

You could use the worksheet_Calculate event instead:

Option Explicit
Private Sub Worksheet_Calculate()
If Me.Range("b202").Value = True Then
Me.Range("b201").Value = True
End If
End Sub

ps.

This formula:
=IF($E$12=3,TRUE,FALSE)
is equivalent to:
=$E$12=3

pps. I don't know why you don't want to use a worksheet formula, but that would
seem like a much more natural fit to me.
Well, bummer, I thought I had it working, then I went and changed something
and now it isn't. Here's the code I'm using:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("B202"))
If Not isect Is Nothing Then
If Target = True Then
[B201] = True
End If
End If
End Sub

In cell B202 I have this formula: =IF($E$12=3,TRUE,FALSE) now, when E12=3,
that cell becomes TRUE, but nothing happens in B201? Do I have it right?
I've also tried redirecting it to a completely new cell, C202, by changing
this line:

[B201] = True

so that it points to C202, which has never been used:

[C202] = True

and nothing happens there either, does the target cell need to have
something in it to accept the new value?

Thanks again.

Conf.

Shane Devenshire said:
Hi,

Here is some sample code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("B21"))
If Not isect Is Nothing Then
IF Target = TRUE Then
[A13]=True
End If
End If
End Sub
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


Confused said:
I have an IF statement that works exactly the way I want and results in a
TRUE/FALSE. The problem is, I want that true/false result to be directed
to
another cell without having to make that cell have a formula in it like
=A13
(if A13 is where I have the IF formula). Essentially, I want the target
cell
to be able to accept data from a couple of sources, all being true/false
tests, and not have a formula in it. Does that make sense? Something
like:
IF B21=TRUE then A13=TRUE, which is easy in VBA, but seems I can't get
Excel
to do the same.

Thanks
Conf.
Click to expand...
Click to expand...
Click to expand...
 
Ah, thanks, that is closer to what I need, but maybe more explanation is in
order. When you say that:

<<<
This formula:
=IF($E$12=3,TRUE,FALSE)
is equivalent to:
=$E$12=3
E12 is the result of another series of calculations, it's range is 1-6, the
only time I want the cell to change is when it equals 3. So, the result of
B202 (where the above formula is) is usually FALSE, but when E12=3 then it's
TRUE, and at that point, I want B201 to also equal TRUE, but the contents of
B201 can also be changed by other actions on the user's part. Mostly, I'm
trying to make this as user-friendly and error-proof as possible. If the
user selects a specific option by check box, B201 will go TRUE
automatically. But, if through other means the user ends up with E12=3, then
that will also result in B201 equaling true.

Your supplied script (Thank you very much by the way) works great, except
for one tiny problem. Say for instance, the user creates a situation where
E12=3, the script makes B201 go to true like it should, but if the user then
changes something and E12 no longer equals 3, it doesn't change B201 back to
FALSE.

In a nutshell, the user needs to be able to change B201 to true or false at
any time by check box and switch the state as many times as needed, but when
other options cause E12 to equal 3, then it needs to change automatically,
and return to false if the user changes other values so that E12 no longer
equals 3.

Hope that helped explain things better.


Conf.


Dave Peterson said:
Your cell's value changes as the result of a formula.

The worksheet_Change event fires when someone makes a change manually
(mouse or
keyboard). So this _change event isn't firing.

You could use the worksheet_Calculate event instead:

Option Explicit
Private Sub Worksheet_Calculate()
If Me.Range("b202").Value = True Then
Me.Range("b201").Value = True
End If
End Sub

ps.

This formula:
=IF($E$12=3,TRUE,FALSE)
is equivalent to:
=$E$12=3

pps. I don't know why you don't want to use a worksheet formula, but that
would
seem like a much more natural fit to me.
Well, bummer, I thought I had it working, then I went and changed
something
and now it isn't. Here's the code I'm using:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("B202"))
If Not isect Is Nothing Then
If Target = True Then
[B201] = True
End If
End If
End Sub

In cell B202 I have this formula: =IF($E$12=3,TRUE,FALSE) now, when
E12=3,
that cell becomes TRUE, but nothing happens in B201? Do I have it right?
I've also tried redirecting it to a completely new cell, C202, by
changing
this line:

[B201] = True

so that it points to C202, which has never been used:

[C202] = True

and nothing happens there either, does the target cell need to have
something in it to accept the new value?

Thanks again.

Conf.

Shane Devenshire said:
Hi,

Here is some sample code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim isect As Range
Set isect = Application.Intersect(Target, Range("B21"))
If Not isect Is Nothing Then
IF Target = TRUE Then
[A13]=True
End If
End If
End Sub
--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


:

I have an IF statement that works exactly the way I want and results
in a
TRUE/FALSE. The problem is, I want that true/false result to be
directed
to
another cell without having to make that cell have a formula in it
like
=A13
(if A13 is where I have the IF formula). Essentially, I want the
target
cell
to be able to accept data from a couple of sources, all being
true/false
tests, and not have a formula in it. Does that make sense? Something
like:
IF B21=TRUE then A13=TRUE, which is easy in VBA, but seems I can't get
Excel
to do the same.

Thanks
Conf.
Click to expand...
Click to expand...
Click to expand...
 
Confused said:
In a nutshell, the user needs to be able to change B201 to true or false at
any time by check box and switch the state as many times as needed, but when
other options cause E12 to equal 3, then it needs to change automatically,
and return to false if the user changes other values so that E12 no longer
equals 3.
Click to expand...
....

So what should happen if other entries are such that E12 = 3 and B201
is TRUE, and the user sets B201 to FALSE? Should your workbook change
B201 back to TRUE? Should it change E12?
 
Harlan Grove said:
...

So what should happen if other entries are such that E12 = 3 and B201
is TRUE, and the user sets B201 to FALSE? Should your workbook change
B201 back to TRUE? Should it change E12?
Click to expand...

If E12=3, then B201 must be true, it is a requirement for that option. But,
B201 can also be set to true by the user for any other option as well, but
should not be allowed to change B201 to false if E12=3.

Essentially, this is a price quote sheet. E12 reflects 6 options selected by
option buttons. There are about 20 check boxes with various additional
options as well. Think automobile models, the six options are "car",
"truck", "minivan" etc. Option number 3 is say, "sports car". The other 20
check boxes are for things like "sunroof", "V6 engine", and paint colors.
Let's say option 3, the sports car, only comes in red paint, and check box
seven, which outputs to B201 as true/false is for red paint. Of course, any
of the other car models can be ordered in red paint, but if you pick the
sports car, it MUST come in red paint, so check box seven should
automatically become checked if that option is picked. But, if that option
(sports car) is no longer picked, the red paint check box should go back
off, but be available to be checked for any of the other options.

I can get it to work simply with an if statement as far as the price quote
goes, but making the check box appear checked so that the user knows that it
is required for that option and the reason for the price increase (the
customer will ask, I just know it) is what I'm after.

Thanks for all the help everyone
Conf.
 
Confused said:
If E12=3, then B201 must be true, it is a requirement for that option. But,
B201 can also be set to true by the user for any other option as well, but
should not be allowed to change B201 to false if E12=3.
Click to expand...
....

This is a flawed approach. You should use another cell to make a final
determination using a formula like

=OR(E12=3,B201)

That way the result of the formula in E12 or the user entry in B201
would trigger the appropriate action while also eliminating any
potential problems that could arise if E12 = 3 but the user insists on
entering FALSE in B201.

I realize you'd like the checkbox associated with B210 to appear
checked when E12 = 3, but from my own experience I believe you'd have
far fewer headaches using another cell to contain the final status and
maybe add another cell formula to display an optional message when the
user unchecks the checkbox associated with cell B201 when E12 = 3,
something like

=IF(AND(E12=3,NOT(B201)),"You selected SPORTS CAR, which must come in
red even if you clear this checkbox. You have no choice. HAND","")
 
Harlan Grove said:
...

This is a flawed approach. You should use another cell to make a final
determination using a formula like
Click to expand...

LOL, if any approach is flawed, it's mine! I was thrust into Excel because
I'm the "computer guy". So I'm going by feel mostly.
=OR(E12=3,B201)

That way the result of the formula in E12 or the user entry in B201
would trigger the appropriate action while also eliminating any
potential problems that could arise if E12 = 3 but the user insists on
entering FALSE in B201.

I realize you'd like the checkbox associated with B210 to appear
checked when E12 = 3, but from my own experience I believe you'd have
far fewer headaches using another cell to contain the final status and
maybe add another cell formula to display an optional message when the
user unchecks the checkbox associated with cell B201 when E12 = 3,
something like

=IF(AND(E12=3,NOT(B201)),"You selected SPORTS CAR, which must come in
red even if you clear this checkbox. You have no choice. HAND","")
Click to expand...

Yes, something like that may be the best solution, I had already figured the
formula for the pricing, I was trying to avoid the inevitable confusion.

Thanks again for all the help, everyone.

Conf.
 
Back
Top