Linking Concatenate text

[Scott]

I have a formula that reads as follows:

=CONCATENATE(" NUMBER OF SPRING WINDS : ",IF
($E$6=1,$X$4,IF($E$6=2,$X$5,IF($E$6=3,$X$6,IF
($E$6=4,$X$7,IF($E$6=5,$X$8,IF($E$6=6,$X$9,IF
($E$6=7,$X$10))))))))

I would like to add more values of the $E$6 variable, but
am limited (I believe) by the 30 character limit of the
Concatenate function. How would I add more characters to
allow $E$6=8, etc....

Thanks,
Scott

 

[Frank Kabel]

Hi
the problem is not the length but the nested function level. You can't
nest more than 8 functions. In your case I would use:
=" NUMBER OF SPRING WINDS : " & OFFSET($X$3,$E$6,0)

 

[Guest]

Would I still use the concentate function? How would I
proceed with the Offset string to be able to allow up to
30 options for $e$6?

Thanks,
Scott

 

[Trevor Shuttleworth]

Scott

you are limited to seven IFs.

One way:

=CONCATENATE(" NUMBER OF SPRING WINDS : ",INDIRECT("X" & $E$6+3))

Regards

Trevor

 

[Frank Kabel]

Hi
no need for CONCATENATE. The & Operator does the same but does not use
a function call. So
=CONCATENATE(A1,A2)
could be replaced with
=A1&A2

For your other question: If your logic follows the first example the
formula I gave you should just do this. Have you tried it?

 

[Trevor Shuttleworth]

Scott

you are limited to seven IFs.

another variation:

=" NUMBER OF SPRING WINDS : " & INDIRECT("X" & $E$6+3))

Regards

Trevor

 

[Scott]

Not sure this went through? Noted you suggest using
the "&" to link the strings. However what does this
actually produce; OFFSET($X$3,$E$6,0)?

 

[Frank Kabel]

Hi
OFFSET in this cases 'shifts' the reference X3 by the number of rows
indicated in E6. So if E6 contains '2' the reference X3 is shifted by
two rows -> X5

 

[Trevor Shuttleworth]

Scott

it uses X3 as the "base" cell and uses the value in E6 as an offset. In
other words, if E6 has a value of 3, it starts with cell X3 and goes down
three rows to X6 and returns the value in that cell; 4 takes it down to cell
X7, etc.

Regards

Trevor

 

[Scott]

Frank,

Thank you for your posts your suggestion works perfectly
for my Excel application.

Thanks,
Scott