Formula nesting help

[Dan88]

Hello Everyone, i have the following formula that compares 2 sheets and returns Yes Or NO.

The formula is auto filled entire column. So if my data spans from AD3 to AD 80, i will have 8 rows of "No". iS there a way to blank this out?


=IF(ISNA(VLOOKUP(A11, 'SP'!$AD$3:$AD$88,1,FALSE)), "No", "Yes")


Thanks

 

[brownbread]

You can nest the whole thing inside a blank check function:

=IF(A11="","",IF(ISNA(VLOOKUP(A11, 'SP'!$AD$3:$AD$88,1,FALSE)), "No", "Yes"))

 

[Dan88]

Thanks for the quick reply brown bread!

I added additional "" to NO and Yes but now when i run the vba, it fills the cells with FALSE rather than a YES or NO. Any other thing im doing wrong:

Range("I2").Formula = "=IF(A11="","",IF(ISNA(VLOOKUP(A11, 'SP'!$AD$3:$AD$88,1,FALSE)), ""No"", ""Yes""))"

Thanks

 

[Joe4]

It needs to be:

[COLOR=#333333]Range("I2").Formula = "=IF(A11=[/COLOR][COLOR=#ff0000]"""",""""[/COLOR][COLOR=#333333],IF(ISNA(VLOOKUP(A11, 'SP'!$AD$3:$AD$88,1,FALSE)), ""No"", ""Yes""))"[/COLOR]

(you need to double-up on the double-quotes, because they are used both as text qualifiers in VBA code and literal values in your formula).

This often gets confusing, but there is an easy way to get the code that you need. Turn on your Macro Recorder and record yourself entering the formula on the sheet, then stop the Macro Recorder and view your code. This will show you how the code needs to be written in VBA.

 

[Dan88]

Joe4, BrownBread you guys are both LEGENDS!

It worked. THanks so much for the pointers! Much Much appreciated.
I knew it had to do with addtional qoutes so i added one to the No and Yes but didnt think to add it to the code BrownBread did for me.

Awesome. Thanks again!