function to populate a cell where formula is NOT located

[Jim]

I'm using excel 2007.

I'm looking to have a logical test in one cell that will populate a
different cell if true, or a third cell if false.

For example, the logical test located in cell A1: If (A2=3, then Cell B2 =
"True", then Cell B2="RED")

Another variation is to have the value in a cell determine the new cell
location. For example, IF(D1="","", then cell A(Row Number D1)="Populated")


Solutions?

 

[Gary''s Student]

In your first example, put a formula in B2 to test the value in A1.
In your second example, you could use a simple event macro to dynamically
test the value in D1 and set the appropriate cell in column A

 

[Jim]

I don't want to put the formulas is the 'destination' cells as that area is
very dynamic. I made an error in my first example, the value of false should
have been 'then Cell C2="Red"'.

I've had a little feedback from another source saying this is most likely
solved by VBA and not in functions or formulas.

 

[Gary''s Student]

Is a VBA solution acceptable to you? Some posters are "macrophobic"

 

[Gary''s Student]

Let's say we are setting D1 manually. Install the following event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim t As Range
Set t = Target
If Intersect(t, Range("D1")) Is Nothing Then Exit Sub
If t.Value = "" Then Exit Sub
Application.EnableEvents = False
Range("A" & t.Value).Value = "Populated"
Application.EnableEvents = True
End Sub

If you enter 10 in D1, then A10 will be populated, etc.

Because it is worksheet code, it is very easy to install and automatic to use:

1. right-click the tab name near the bottom of the Excel window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you have any concerns, first try it on a trial worksheet.

If you save the workbook, the macro will be saved with it.


To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


If D1 contains a formula, then a slightly different macro would be needed.

 

[Rick Rothstein]

Formulas on a worksheet cannot push a value into another cell... they can
only display their calculated value (text or number) in the cell they are
located in. Putting formulas in what you refer to as the "destination cell"
and having them determine what should be displayed in there is pretty much
how spreadsheets work. Can you explain what you mean by "that area is very
dynamic" and why you think this means you cannot place your formula there?
Yes, VBA gives you much more flexibility, but from what you have described
so far, I am having trouble seeing why you can't do this with simple
worksheet formulas.

 

[Bernd P]

Hello Jim,

One possible approach might be a conditional format.

The Excel help will give you more detailed information on this.

Regards,
Bernd