Formular Is Too Long

K

koenigma

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(LOOKUP(CJ27,FNums),"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="Without")),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
 
K

koenigma

Your right I should have explained what it does. I have put a
simplified version below.

It is comparing the value of a single cell to multiple values, it is
based upon a Lookup Worksheet that has a 2 Column range defined as
FNums

1 1
2 2
3 3
4 4
5 5
6 6
21 7
31 8
32 9
34 10
41 10
42 10
43 10
44 10
51 11
61 12
71 13

=IF(ISNUMBER(1/(LOOKUP(CJ27,FNums)))CHOOSE(LOOKUP(CJ27, FNum),"Text
based upon results of lookup", "Text based upon reslults of lookup",
etc......), "CJ27")

Martin
 
B

Bob Phillips

I mananged to get it in like this.

First add a defined name, Insert>Name>Define... 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)

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(LOOKUP(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="Without"
)),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
 
P

Pete_UK

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,FNums),"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
 
P

Pete_UK

I've just spotted two other "Filler Metals" in the middle, but I'm sure
you get the drift.

Pete
 
B

Bob Phillips

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)

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
 
K

koenigma

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

Martin
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Scrabble Value calculation for Welsh words 0
Tough teaser! 10
New to VBA 6
Get/Find criteria and copy sum 2
Storing Multiple Selected Pivot Table Report Filter Values 2
FUNCTION IF 1
Sum work hours to hours & nearest 10th of an hour 14
lookup two tables 7

Top