#value! Error

[tallpaul]

From a drop down list of 15 names in A2, B1 returns a value of 1-1
depending on which name selected.
The idea is to show in column D the names of upto 24 players, the nam
chosen in A2 has played.
I use the following formula. ( a nested IF only allows 7 options??)
=IF($B$1=1,I2,0)+IF($B$1=2,L2,0)+IF($B$1=3,O2,0)+IF($B$1=4,R2,0)+IF($B$1=5,U2,0)+IF($B$1=6,X2,0)+IF($B$1=7,AA2,0)+IF($B$1=8,AD2,0
& so on to +IF($B$1=15,AW2,0)
This formula is repeated 24 times D4 through D27 to return text fro
cell reference I2, L2, O2 & so on.
It returns always returns #value! if I change text to numbers it work
fine?????
Please advise
Cheers
tallpau

Attachment filename: bowls results.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=51615

 

[JE McGimpsey]

The + operator will return #VALUE! if either of its operands are text.
You could use the concatenation operator, instead (&).

However, in this case you should use a calculated value:

D2: =OFFSET(G2,0,$B$1*2)