Hiding false statement using if

[dddaniels]

Hello all, searched the net and tried a number of ways posted but nothing works. Please HELP.


=IF(B3309=1,Sheet1!$B$5,IF(B3309=2,Sheet1!$B$6,IF(B3309=3,Sheet1!$B$7,IF(B3309=4,Sheet1!$B$8,IF(B3309=5,Sheet1!$B$9,IF(B3309=6,Sheet1!$B$10,IF(B3309=7,Sheet1!$B$11,IF(B3309=8,Sheet1!$B$12,IF(B3309=9,Sheet1!$B$13,IF(B3309=10,Sheet1!$B$14,IF(B3309=11,Sheet1!$B$15,IF(B3309=12,Sheet1!$B3310,IF(B3309=13,Sheet1!$B$17,IF(B3309=14, Sheet1!$B$18,IF(B3309=15, Sheet1!$B$19, IF(B3309="1A",Sheet1!$F$5,IF(B3309="2a",Sheet1!$F$6,IF(B3309="3a",Sheet1!$F$7,IF(B3309="4a",Sheet1!$F$8,IF(B3309="5a",Sheet1!$F$9,IF(B3309="6a",Sheet1!$F$10,IF(B3309="7a",Sheet1!$F$11,IF(B3309="8a",Sheet1!$F$12,IF(B3309="9a",Sheet1!$F$13,IF(B3309="10a",Sheet1!$F$14,IF(B3309="11a",Sheet1!$F$15,IF(B3309="12a",Sheet1!$F$16,IF(B3309="13a",Sheet1!$F$17,IF(B3309="14a",Sheet1!$F$18,IF(B3309="15a",Sheet1!$F$19,IF(B3309="1b",Sheet1!$I$5,IF(B3309="2b",Sheet1!$I$6,IF(B3309="3b",Sheet1!$I$7,IF(B3309="4b",Sheet1!$I$8,IF(B3309="5b",Sheet1!I$9,IF(B3309="6b",Sheet1!$I$10,IF(B3309="7b",Sheet1!$I$11,IF(B3309="8b",Sheet1!$I$12,IF(B3309="9b",Sheet1!$I$13,IF(B3309="10b",Sheet1!$I$14,IF(B3309="11b",Sheet1!$I$15,IF(B3309="12b",Sheet1!$I$16,IF(B3309="13b",Sheet1!$I$17,IF(B3309="14b",Sheet1!$I$18,IF(B3309="15b",Sheet1!$I$19,IF(B3309="1c",Sheet1!$K$5,IF(B3309="2c",Sheet1!$K$6,IF(B3309="3c",Sheet1!$K$7,IF(B3309="4c",Sheet1!$K$8,IF(B3309="5c",Sheet1!$K$9,IF(B3309="6c",Sheet1!$K$10,IF(B3309="7c",Sheet1!$K$11,IF(B3309="8c",Sheet1!$K$12,IF(B3309="9c",Sheet1!$K$13,IF(B3309="10c",Sheet1!$K$14,IF(B3309="11c",Sheet1!$K$15,IF(B3309="12c",Sheet1!$K$16,IF(B3309="13c",Sheet1!$K$17,IF(B3309="14c",Sheet1!$K$18,IF(B3309="15c",Sheet1!$K$19))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

 

[Michael M]

See the red quotes at the end of the formula

=IF(B3309=1,Sheet1!$B$5,IF(B3309=2,Sheet1!$B$6,IF(B3309=3,Sheet1!$B$7,IF(B3309=4,Sheet1!$B$8,IF(B3309=5,Sheet1!$B$9,IF(B3309=6,Sheet1!$B$10,IF(B3309=7,Sheet1!$B$11,IF(B3309=8,Sheet1!$B$12,IF(B3309=9,Sheet1!$B$13,IF(B3309=10,Sheet1!$B$14,IF(B3309=11,Sheet1!$B$15,IF(B3309=12,Sheet1!$B3310,IF(B3309=13,Sheet1!$B$17,IF(B3309=14,Sheet1!$B$18,IF(B3309=15,Sheet1!$B$19,IF(B3309="1A",Sheet1!$F$5,IF(B3309="2a",Sheet1!$F$6,IF(B3309="3a",Sheet1!$F$7,IF(B3309="4a",Sheet1!$F$8,IF(B3309="5a",Sheet1!$F$9,IF(B3309="6a",Sheet1!$F$10,IF(B3309="7a",Sheet1!$F$11,IF(B3309="8a",Sheet1!$F$12,IF(B3309="9a",Sheet1!$F$13,IF(B3309="10a",Sheet1!$F$14,IF(B3309="11a",Sheet1!$F$15,IF(B3309="12a",Sheet1!$F$16,IF(B3309="13a",Sheet1!$F$17,IF(B3309="14a",Sheet1!$F$18,IF(B3309="15a",Sheet1!$F$19,IF(B3309="1b",Sheet1!$I$5,IF(B3309="2b",Sheet1!$I$6,IF(B3309="3b",Sheet1!$I$7,IF(B3309="4b",Sheet1!$I$8,IF(B3309="5b",Sheet1!I$9,IF(B3309="6b",Sheet1!$I$10,IF(B3309="7b",Sheet1!$I$11,IF(B3309="8b",Sheet1!$I$12,IF(B3309="9b",Sheet1!$I$13,IF(B3309="10b",Sheet1!$I$14,IF(B3309="11b",Sheet1!$I$15,IF(B3309="12b",Sheet1!$I$16,IF(B3309="13b",Sheet1!$I$17,IF(B3309="14b",Sheet1!$I$18,IF(B3309="15b",Sheet1!$I$19,IF(B3309="1c",Sheet1!$K$5,IF(B3309="2c",Sheet1!$K$6,IF(B3309="3c",Sheet1!$K$7,IF(B3309="4c",Sheet1!$K$8,IF(B3309="5c",Sheet1!$K$9,IF(B3309="6c",Sheet1!$K$10,IF(B3309="7c",Sheet1!$K$11,IF(B3309="8c",Sheet1!$K$12,IF(B3309="9c",Sheet1!$K$13,IF(B3309="10c",Sheet1!$K$14,IF(B3309="11c",Sheet1!$K$15,IF(B3309="12c",Sheet1!$K$16,IF(B3309="13c",Sheet1!$K$17,IF(B3309="14c",Sheet1!$K$18,IF(B3309="15c",Sheet1!$K$19,[color=red]""[/color]))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

 

[tygrrboi]

Does this do what you want?

=INDIRECT("Sheet1!" & ADDRESS([COLOR=#0000ff]IF(ISNUMBER(B3309), B3309, INT(LEFT(B3309, LEN(B3309)-1)[/COLOR]+4)), IF(RIGHT(B3309,1)="a", [COLOR=#ffa500]6[/COLOR], IF(RIGHT(B3309,1)="b", [COLOR=#ff0000]9[/COLOR], IF(RIGHT(B3309,1)="c",[COLOR=#ee82ee]11[/COLOR],[COLOR=#daa520]2[/COLOR])))))

IF(ISNUMBER(B3309), B3309, INT(LEFT(B3309, LEN(B3309)-1) is just the number part of the contents of B3309

number for column:
F
I
K
B

 

[tygrrboi]

sorry. Slight mistake

=INDIRECT("Sheet1!" & ADDRESS(IF(ISNUMBER(B3309), B3309, INT(LEFT(B3309, LEN(B3309)[COLOR=#ff0000]-1)))+4[/COLOR], IF(RIGHT(B3309,1)="a", 6, IF(RIGHT(B3309,1)="b", 9, IF(RIGHT(B3309,1)="c",11,2)))))

This is correct

 

[dddaniels]

So simple, I was over thinking it.........Thanks for your assistance.

 

[thisoldman]

Here's another possible formula. Like tygrrboi's solution, it eliminates most of the nested conditionals.

=IFERROR(INDEX($B$5:$K$19,LEFT(B3309,LEN(B3309)-ISTEXT(B3309)),ISNUMBER(B3309)+(RIGHT(B3309)="a")*5+(RIGHT(B3309)="b")*8+(RIGHT(B3309)="c")*10),"")