Need formula to return yes or no

May 13, 2011 at 11:58:18
Specs: Windows XP
I have a large excel spreadsheet showing contact info and other data for our borrowers. There are 6 columns with borrower contacts. For example phone 1 may contain home number, phone 2 may contain a work number, etc. I have added a column and want it to show if I have a contact number in any of these columns. I don't need the number or how many numbers, just yes or no if we have a phone number. The colums are not side by side, other columns in between show the type of contact and dates. I have tried =If(BD:BD,BG:BG,BJ:BJ,BM:BM,BP:BP,BS:BS),"YES" or "NO" Please help!!!!!!!

See More: Need formula to return yes or no

Report •


#1
May 13, 2011 at 12:07:10
I'm not sure why you are trying to use entire columns (e.g. BD:BD).

Aren't you trying to check for contact numbers in various columns but on a row by row basis? i.e. Each customer has it's own row and you are checking to see which customers have contact information and which don't?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
May 13, 2011 at 12:22:15
Yes, thats right, I want to know which customers row by row have a contact number. I thought this was what I needed to use, but please correct me with the right way to enter the formula for my desired results.
Thanks!!!!!!

Report •

#3
May 13, 2011 at 12:38:30
You need to check each row individually. This will check Row 1. Drag it down your column and the 1 will increment row by row.

It basically checks each cell (in order) to see if it contains data, returning YES as soon as it finds any cell that is not empty.

=IF(OR(BD1<>"",BG1<>"",BJ1<>"",BM1<>"",BP1<>"",BS1<>""),"YES","NO")

You could also use:

=IF(COUNTA(BD1, BG1, BJ1, BM1, BP1, BS1)>0,"YES","NO")

COUNTA returns the number of cells in a range that contain data, therefore if COUNTA returns any number greater than 0, there must be data in at least one of those cells.

BTW Neither of those functions will tell if the cells contain an actual contact number. They will simply tell you if any of those cells contain data. Checking for a phone number would be a bit more difficult.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
May 13, 2011 at 13:14:00
Wahoo!!!!! It works. Thanks for you help. I don't have to work the weekend now!

Report •


Ask Question