|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
November 12th, 2009, 12:10 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
Check for text in multiple cells
Hello,
I would like to check multiple cells for a name and, if the name appears in any of them, print that name to the destination cell. For instance, columns A, C, E, G, and I contain names. I want to check A1, C1, E1, G1, and I1 for "Bill" and, if "Bill" is present in any of the, print "Bill" in K1. Each name should only appear in one of the columns in a row, so if "Bill' is in C1, it won't be in A, E, G, or I 1. Can this be done? Thanks Tom 
|
|
#2
November 12th, 2009, 12:52 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Check for text in multiple cells
Not elegant, but it will work:
=IF(IF(A1="Bill",1,0)+IF(C1="Bill",1,0)+IF(E1="Bil l",1,0)+IF(G1="Bill",1,0)+IF(I1="Bill",1,0)=1,"Jus t One Bill","") of course you could substitute a cell address where you'd type in a name for "Bill" in all of the formulas. "tommcbrny" wrote: Hello, I would like to check multiple cells for a name and, if the name appears in any of them, print that name to the destination cell. For instance, columns A, C, E, G, and I contain names. I want to check A1, C1, E1, G1, and I1 for "Bill" and, if "Bill" is present in any of the, print "Bill" in K1. Each name should only appear in one of the columns in a row, so if "Bill' is in C1, it won't be in A, E, G, or I 1. Can this be done? Thanks Tom
|
|
#3
November 12th, 2009, 02:26 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Check for text in multiple cells
You can try the below formula in cell K1
=IF(SUMPRODUCT((MOD(COLUMN(A1:I1),2)=1)*(A1:I1="Bi ll")),"Bill","") If this post helps click Yes --------------- Jacob Skaria "tommcbrny" wrote: Hello, I would like to check multiple cells for a name and, if the name appears in any of them, print that name to the destination cell. For instance, columns A, C, E, G, and I contain names. I want to check A1, C1, E1, G1, and I1 for "Bill" and, if "Bill" is present in any of the, print "Bill" in K1. Each name should only appear in one of the columns in a row, so if "Bill' is in C1, it won't be in A, E, G, or I 1. Can this be done? Thanks Tom
|
|
#4
November 12th, 2009, 03:08 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Check for text in multiple cells
Works well, thank you. Is there a way to substitute a list of names for
"Bill" so that any name in the list "Names" will be printed if found in the cells checked? "Jacob Skaria" wrote: You can try the below formula in cell K1 =IF(SUMPRODUCT((MOD(COLUMN(A1:I1),2)=1)*(A1:I1="Bi ll")),"Bill","") If this post helps click Yes --------------- Jacob Skaria "tommcbrny" wrote: Hello, I would like to check multiple cells for a name and, if the name appears in any of them, print that name to the destination cell. For instance, columns A, C, E, G, and I contain names. I want to check A1, C1, E1, G1, and I1 for "Bill" and, if "Bill" is present in any of the, print "Bill" in K1. Each name should only appear in one of the columns in a row, so if "Bill' is in C1, it won't be in A, E, G, or I 1. Can this be done? Thanks Tom
|
|
#5
November 12th, 2009, 03:10 PM
posted to microsoft.public.excel.misc
|
|||
|
|||
|
Check for text in multiple cells
Definitely works, thank you. Can I sub in a list of names for "Bill", so
that any name in the list "Names" will be printed if contained in the cells checked? "JLatham" wrote: Not elegant, but it will work: =IF(IF(A1="Bill",1,0)+IF(C1="Bill",1,0)+IF(E1="Bil l",1,0)+IF(G1="Bill",1,0)+IF(I1="Bill",1,0)=1,"Jus t One Bill","") of course you could substitute a cell address where you'd type in a name for "Bill" in all of the formulas. "tommcbrny" wrote: Hello, I would like to check multiple cells for a name and, if the name appears in any of them, print that name to the destination cell. For instance, columns A, C, E, G, and I contain names. I want to check A1, C1, E1, G1, and I1 for "Bill" and, if "Bill" is present in any of the, print "Bill" in K1. Each name should only appear in one of the columns in a row, so if "Bill' is in C1, it won't be in A, E, G, or I 1. Can this be done? Thanks Tom
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
