|
Formular Is Too Long
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,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="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 |
Formular Is Too Long
I am not quite sure what this is , Maybe you could check out this site VLOOKUP and see if it could help you, http://www.contextures.com/xlFunctions02.html -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=574439 |
Formular Is Too Long
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 davesexcel wrote: I am not quite sure what this is , Maybe you could check out this site VLOOKUP and see if it could help you, http://www.contextures.com/xlFunctions02.html -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=574439 |
Formular Is Too Long
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 |
Formular Is Too Long
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 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 |
Formular Is Too Long
I've just spotted two other "Filler Metals" in the middle, but I'm sure
you get the drift. Pete Pete_UK wrote: 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 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 |
Formular Is Too Long
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 |
Formular Is Too Long
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 |
| All times are GMT +1. The time now is 01:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com