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!!!!!!!

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.

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!!!!!!

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.

Wahoo!!!!! It works. Thanks for you help. I don't have to work the weekend now!

Ask Your Question

Weekly Poll

Do you think Google should sell budget phones in the US?

Discuss in The Lounge

Poll History