Formula to populate either "Up" or "Down" based on location of entry in Column A

[y3tter]

I have three columns. Only one cell in column A will have a value, which will be "Active". Column B is locations. In column C I'm trying to get anything that at or below the "Active" indicator to populate as "Down" and anything above the "Active" marker to populate as "Up".

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Minn[/TD]
[TD]Up[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Lars[/TD]
[TD]Up[/TD]
[/TR]
[TR]
[TD]Active[/TD]
[TD]X5[/TD]
[TD]Down[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Lars_1[/TD]
[TD]Down[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Y2[/TD]
[TD]Down[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Z6[/TD]
[TD]Down[/TD]
[/TR]
</tbody>[/TABLE]

 

[mumps]

Assuming data starts in row 1, try:

Sub y3etter()
    Application.ScreenUpdating = False
    Dim LastRow As Long, fnd As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    fnd = Range("A:A").Find("Active").Row
    Range("C1:C" & fnd - 1) = "Up"
    Range("C" & fnd & ":C" & LastRow) = "Down"
    Application.ScreenUpdating = True
End Sub

 

[tyija1995]

Hey,

try this formula in C1 and drag it down:

=IF(COUNTIF($A$1:A1,"Active")=1,"Down","Up")

 

[y3tter]

Hey,

try this formula in C1 and drag it down:

=IF(COUNTIF($A$1:A1,"Active")=1,"Down","Up")

Thank you, this is exactly what I was looking for!!!

 

[y3tter]

Assuming data starts in row 1, try:

Sub y3etter()
    Application.ScreenUpdating = False
    Dim LastRow As Long, fnd As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    fnd = Range("A:A").Find("Active").Row
    Range("C1:C" & fnd - 1) = "Up"
    Range("C" & fnd & ":C" & LastRow) = "Down"
    Application.ScreenUpdating = True
End Sub

Thank you!!

 

[mumps]

My pleasure.