function to populate a cell where formula is NOT located
in [Functions]
|
Prev: Dynamic Table Problem
Next: SUMIF returns no value
From: Jim on 4 Apr 2010 08:05 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?
From: Gary''s Student on 4 Apr 2010 08:16 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 -- Gary''s Student - gsnu201001 "Jim" wrote: > 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?
From: Jim on 4 Apr 2010 08:23 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" wrote: > 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 > -- > Gary''s Student - gsnu201001 > > > "Jim" wrote: > > > 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?
From: Gary''s Student on 4 Apr 2010 08:57 Is a VBA solution acceptable to you? Some posters are "macrophobic" -- Gary''s Student - gsnu201001 "Jim" wrote: > 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" wrote: > > > 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 > > -- > > Gary''s Student - gsnu201001 > > > > > > "Jim" wrote: > > > > > 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?
From: Gary''s Student on 4 Apr 2010 09:09
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. -- Gary''s Student - gsnu201001 "Jim" wrote: > 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" wrote: > > > 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 > > -- > > Gary''s Student - gsnu201001 > > > > > > "Jim" wrote: > > > > > 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? |