Re: #value! Error

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: JE McGimpsey (jemcgimpsey_at_mvps.org)
Date: 04/18/04 

Date: Sun, 18 Apr 2004 17:22:23 -0600

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)

In article <tallpaul.14xh65@excelforum-nospam.com>,
 tallpaul <<tallpaul.14xh65@excelforum-nospam.com>> wrote:

> From a drop down list of 15 names in A2, B1 returns a value of 1-15
> depending on which name selected.
> The idea is to show in column D the names of upto 24 players, the name
> 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 from
> cell reference I2, L2, O2 & so on.
> It returns always returns #value! if I change text to numbers it works
> fine?????
> Please advise