Thanks for the help, I took the easier approach and just used nested
IF's. But I will give your suggestion a try.

Martin
Bob Phillips wrote:
You're just too conscientious Pete, I just did enough to get it in <G

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Pete_UK" wrote in message
oups.com...
You could make use of two other named cells to shorten it even more.
Put the text " With & Without Backing" in a cell somewhere and name
this cell W. Put the text " Filler Metals" into another cell and name
this F. Then, wherever the text appears in the formula you can
substitute the named cell, as follows:

=IF(InFNums*(CJ13="With"),"F-N° "&CHOOSE(LOOKUP(CJ27,FNums),"1","1, &
2","1,2, & 3","1, 2, 3 & 4","1, 2, 3, 4 & 5")&" With
Backing",IF(InFNums*(OR(CJ13="With",CJ13="Without" )),CHOOSE(LOOKUP(CJ27,FNum
s),"F-N°"&W,"F-N°
2"&W&" F-N° 1 With","F-N° 3"&W&", F-N° 1 & 2 With","F-N°4"&W&"
F-N° 1, 2 & 3 With","F-N°5 With & Without F-N° 1, 2, 3, & 4 With All
F-N° 6 Filler Metals","All F-N° 21 through F-N° 25 Filler
Metals","Only F-N° "&CJ27&F,"Only F-N° "&CJ27&F,"Only F-N°
"&CJ27&F,"Only F-N° "&CJ27&F,"Only F-N° "&CJ27&F,"Only F-N°
"&CJ27&F,"F-N° 34, and F-N° 41 through F-N° 45"&F,"All F-N° 51
through F-N° 55"&F,"All F-N° 61"&F,"Only F-N° "&CJ27&F,"Only F-N°
"&CJ27&F),CJ27))

Bit of a sod to maintain, though ! <bg

Hope this helps.

Pete


Bob Phillips wrote:
I mananged to get it in like this.

First add a defined name, InsertNameDefine... of inFNums with a RefersTo
value of

=ISNUMBER(1/(LOOKUP(Sheet1!CJ27,FNums)))

Then use

=IF(InFNums*(CJ13="With"),"F-N° "&CHOOSE(LOOKUP(CJ27,FNums),"1","1, &
2","1,
2, & 3","1, 2, 3 & 4","1, 2, 3, 4 & 5")&" With Backing",
IF(InFNums*(OR(CJ13="With",CJ13="Without")),
CHOOSE(LOOKUP(CJ27,FNums),"F-N° With & Without Backing",
"F-N° 2 With & Without Backing F-N° 1 With",
"F-N° 3 With & Without Backing, F-N° 1 & 2 With",
"F-N°4 With or Without Backing F-N° 1, 2 & 3 With",
"F-N°5 With & Without F-N° 1, 2, 3, & 4 With All F-N° 6 Filler Metals",
"All F-N° 21 through F-N° 25 Filler Metals",
"Only F-N° "&CJ27&" Filler Metals","Only F-N° "&CJ27&" Filler Metals",
"Only F-N° "&CJ27&" Filler Metals","Only F-N° "&CJ27&" Filler Metals",
"Only F-N° "&CJ27&" Filler Metals","Only F-N° "&CJ27&" Filler Metals",
"F-N° 34, and F-N° 41 through F-N° 45 Filler Metals",
"All F-N° 51 through F-N° 55 Filler Metals","All F-N° 61 Filler Metals",
"Only F-N° "&CJ27&" Filler Metals","Only F-N° "&CJ27&" Filler
Metals"),CJ27))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
oups.com...
I tried to enter the following formular into a cell and become the
message "The formular is to long" if I delete some of the values it
works fine. I knew that there was a limit on IF statments but not for
Lookup values. Would anyone know a way around this, or if it is
possible to use VBA to do the same as the formular.


=IF(ISNUMBER(1/(LOOKUP(CJ27,FNums)))*(AND(CJ13="With")),CHOOSE(LO OKUP(CJ27,F
Nums),"F-N°
1 With Backing","F-N° 1, & 2 With Backing","F-N° 1, 2, & 3 With
Backing","F-N° 1, 2, 3, & 4 With Backing","F-N° 1, 2, 3, 4, & 5 With

Backing"),IF(ISNUMBER(1/(LOOKUP(CJ27,FNums)))*(OR(CJ13="With",CJ13="Withou t"
)),CHOOSE(LOOKUP(CJ27,FNums),"F-N°
1 With & Without Backing","F-N° 2 With & Without Backing F-N° 1
With","F-N° 3 With & Without Backing, F-N° 1 & 2 With","F-N°4 With
or Without Backing F-N° 1, 2 & 3 With","F-N°5 With & Without F-N° 1,
2, 3, & 4 With "All F-N° 6 Filler Metals","All F-N° 21 through F-N°
25 Filler Metals","Only F-N° "&CJ27&" Filler Metals","Only F-N°
"&CJ27&" Filler Metals","Only F-N° "&CJ27&" Filler Metals","Only F-N°
"&CJ27&" Filler Metals","Only F-N° "&CJ27&" Filler Metals","Only F-N°
"&CJ27&" Filler Metals","F-N° 34, and F-N° 41 through F-N° 45 Filler
Metals","All F-N° 51 through F-N° 55 Filler Metals","All F-N° 61
Filler Metals","Only F-N° "&CJ27&" Filler Metals","Only F-N° "&CJ27&"
Filler Metals"),CJ27))

Regards
MArtin