IF Function Solved/Closed

Question

Hello, 

I am just starting out with formulas and I need some help. What I need is:

If the value in Sheet 1 B2 is found anywhere in Sheet 2 column B, the cell I am putting the formula in should display Frank DiGiacomo, and otherwise it should display N/A. I tried this formula:

=IF(Sheet1!B169=Sheet2!B:B,"Frank DiGiacomo","N/A")

But it always comes up as false even though the value I am looking for is indeed in column B.

Anyone know how to make it search the entire B column in Sheet 2?

Thanks!!!

Configuration: Windows 7 / Internet Explorer 9.0

venkat1926 · Answer

I wonder whether you can have a formula in a cell to change it own value.

how ever in another empty cell you can have this formula
=IF(COUNTIF(Sheet2!$B:$B,Sheet1!B169)>=1,"Frank DiGiacomo","")

however you can change the value of a cell with a macro . try this macro 

Sub test()
Dim r1 As Range, r2 As Range, x
With Worksheets("sheet1")
Set r1 = .Range("B169")
x = r1.Value
With Worksheets("sheet2")
Set r2 = .Range("B:B")
If WorksheetFunction.CountIf(r2, x) >= 1 Then
End If
End With
r1 = "Frank DiGiacomo"
End With
End Sub

hangtodry · Answer

Awesome! The first formula worked!! Thanks. :)

IF Function

2 responses

Similar discussions

Subscribe To Our Newsletter!