Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using reference column # in IF Statement
A B C D E
1 Bob Sam Tom 2 =if(c3=0," ",row()) 10 0 4 3 0 5 4 4 4 5 8 0 5 5 4 0 0 I want to be able enter "Bob" in A10 and have the reference in the formula in B2 look to A10 to get the column needed for the calculation. If I change to A10 to "Sam" the formula would change to =if(d3=0," ",row(). I also need to be able to copy this formula down into B3, B4 & B5. Any help would be greatly appreciated. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using reference column # in IF Statement
Try this:
=IF(OFFSET(B2,0,MATCH($A$10,$C$1:$E$1))=0,"",OFFSE T(B2,0,MATCH($A$10,$C$1:$E$1))) "tjvols" wrote: A B C D E 1 Bob Sam Tom 2 =if(c3=0," ",row()) 10 0 4 3 0 5 4 4 4 5 8 0 5 5 4 0 0 I want to be able enter "Bob" in A10 and have the reference in the formula in B2 look to A10 to get the column needed for the calculation. If I change to A10 to "Sam" the formula would change to =if(d3=0," ",row(). I also need to be able to copy this formula down into B3, B4 & B5. Any help would be greatly appreciated. Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using reference column # in IF Statement
On Jul 3, 12:43*pm, TomPl wrote:
Try this: =IF(OFFSET(B2,0,MATCH($A$10,$C$1:$E$1))=0,"",OFFSE T(B2,0,MATCH($A$10,$C$1:$*E$1))) "tjvols" wrote: A *B * * * C * * * D * * * E 1 * * * * *Bob * * Sam * * Tom 2 =if(c3=0," ",row()) * *10 * * * * * * 0 * * * * * * *4 3 * * * * *0 * * * 5 * * * 4 4 *4 * * * 5 * * * 8 * * * 0 5 *5 * * * 4 * * * 0 * * * 0 I want to be able enter "Bob" in A10 and have the reference in the formula in B2 look to A10 to get the column needed for the calculation. *If I change to A10 to "Sam" the formula would change to =if(d3=0," ",row(). *I also need to be able to copy this formula down into B3, B4 & B5. Any help would be greatly appreciated. Thanks.- Hide quoted text - - Show quoted text - That kinda works, but it returns the actual value in the column selected. I need the row number instead - ie. if Bob was in A10 the results would be A2 (2), A3 (" "), A4 (4) A5 (5). If Tom was in A10 the results would be A2 (2), A3 (3), A4(" "), A5 (" ") |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using reference column # in IF Statement
Minor change:
=IF(OFFSET(B2,0,MATCH($A$10,$C$1:$E$1))=0,"",MATCH ($A$10,$C$1:$Â*E$1)) "tjvols" wrote: On Jul 3, 12:43 pm, TomPl wrote: Try this: =IF(OFFSET(B2,0,MATCH($A$10,$C$1:$E$1))=0,"",OFFSE T(B2,0,MATCH($A$10,$C$1:$Â*E$1))) "tjvols" wrote: A B C D E 1 Bob Sam Tom 2 =if(c3=0," ",row()) 10 0 4 3 0 5 4 4 4 5 8 0 5 5 4 0 0 I want to be able enter "Bob" in A10 and have the reference in the formula in B2 look to A10 to get the column needed for the calculation. If I change to A10 to "Sam" the formula would change to =if(d3=0," ",row(). I also need to be able to copy this formula down into B3, B4 & B5. Any help would be greatly appreciated. Thanks.- Hide quoted text - - Show quoted text - That kinda works, but it returns the actual value in the column selected. I need the row number instead - ie. if Bob was in A10 the results would be A2 (2), A3 (" "), A4 (4) A5 (5). If Tom was in A10 the results would be A2 (2), A3 (3), A4(" "), A5 (" ") |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Using reference column # in IF Statement
On Jul 3, 1:12*pm, tjvols wrote:
On Jul 3, 12:43*pm, TomPl wrote: Try this: =IF(OFFSET(B2,0,MATCH($A$10,$C$1:$E$1))=0,"",OFFSE T(B2,0,MATCH($A$10,$C$1:$**E$1))) "tjvols" wrote: A *B * * * C * * * D * * * E 1 * * * * *Bob * * Sam * * Tom 2 =if(c3=0," ",row()) * *10 * * * * * * 0 * * * * * * *4 3 * * * * *0 * * * 5 * * * 4 4 *4 * * * 5 * * * 8 * * * 0 5 *5 * * * 4 * * * 0 * * * 0 I want to be able enter "Bob" in A10 and have the reference in the formula in B2 look to A10 to get the column needed for the calculation. *If I change to A10 to "Sam" the formula would change to =if(d3=0," ",row(). *I also need to be able to copy this formula down into B3, B4 & B5. Any help would be greatly appreciated. Thanks.- Hide quoted text - - Show quoted text - That kinda works, but it returns the actual value in the column selected. *I need the row number instead - ie. if Bob was in A10 the results would be A2 (2), A3 (" "), A4 (4) A5 (5). *If Tom was in A10 the results would be A2 (2), A3 (3), A4(" "), A5 (" ")- Hide quoted text - - Show quoted text - I found a solution - =IF(OFFSET(A3,0,MATCH($A$10,$B$1:$D$1,0))<0,ROW() ," ") thanks for your help, I never would have figured it out without it. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I use an IF statement to reference 2 different cells? | Excel Worksheet Functions | |||
Can I use an IF statement to reference 2 different cells? | Excel Worksheet Functions | |||
Can I use an IF statement to reference 2 different cells? | Excel Worksheet Functions | |||
Can I use an IF statement to reference 2 different cells? | Excel Worksheet Functions | |||
Reference multiple cells in if statement | Excel Worksheet Functions |