How to condense a formula that gets a "TOO LARGE" error...

Can someone please help me condense this formula? It is to large to fit in my workbook but I need every part of it.

=IF(p22,n22,q22)&IF(p23,n23,q23)&IF(p24,n24,q24)&IF(p25,n25,q25)&IF(p26,n26,q26)&IF(p27,n27,q27)&IF(p28,n28,q28)&IF(p29,n29,q29)&IF(p30,n30,q30)&IF(p31,n31,q31)&IF(p32,n32,q32)&IF(p33,n33,q33)&IF(p34,n34,q34)&IF(p35,n35,q35)&IF(p36,n36,q36)&IF(p37,n37,q37)&IF(p38,n38,q38)&IF(p39,n39,q39)&IF(p40,n40,q40)&IF(p41,n41,q41)&IF(p42,n42,q42)&IF(p43,n43,q43)&IF(p44,n44,q44)&IF(p45,n45,q45)&IF(p46,n46,q46)&IF(p47,n47,q47)&IF(p48,n48,q48)&IF(p49,n49,q49)&IF(p50,n50,q50)&IF(p51,n51,q51)&IF(p52,n52,q52)&IF(p53,n53,q53)&IF(p54,n54,q54)&IF(p55,n55,q55)&IF(p56,n56,q56)&IF(p57,n57,q57)&IF(p58,n58,q58)&IF(p59,n59,q59)&IF(p60,n60,q60)&IF(p61,n61,q61)&IF(p62,n62,q62)&IF(p63,n63,q63)&IF(p64,n64,q64)&IF(p65,n65,q65)&IF(p66,n66,q66)&IF(p67,n67,q67)&IF(p68,n68,q68)&IF(p69,n69,q69)&IF(p70,n70,q70)&IF(p71,n71,q71)&IF(p72,n72,q72)&IF(p73,n73,q73)&IF(p74,n74,q74)&IF(p75,n75,q75)&IF(p76,n76,q76)&IF(p77,n77,q77)&IF(p78,n78,q78)&IF(p79,n79,q79)&IF(p80,n80,q80)&IF(p81,n81,q81)&IF(p82,n82,q82)&IF(p83,n83,q83)&IF(p84,n84,q84)&IF(p85,n85,q85)&IF(p86,n86,q86)&IF(p87,n87,q87)&IF(p88,n88,q88)&IF(p89,n89,q89)&IF(p90,n90,q90)&IF(p91,n91,q91)&IF(p92,n92,q92)&IF(p93,n93,q93)&IF(p94,n94,q94)&IF(p95,n95,q95)&IF(p96,n96,q96)&IF(p97,n97,q97)&IF(p98,n98,q98)&IF(p99,n99,q99)&IF(p100,n100,q100)&IF(p101,n101,q101)&IF(p102,n102,q102)&IF(p103,n103,q103)&IF(p104,n104,q104)&IF(p105,n105,q105)&IF(p106,n106,q106)&IF(p107,n107,q107)&IF(p108,n108,q108)&IF(p109,n109,q109)&IF(p110,n110,q110)&IF(p111,n111,q111)&IF(p112,n112,q112)&IF(p113,n113,q113)&IF(p114,n114,q114)&IF(p115,n115,q115)&IF(p116,n116,q116)&IF(p117,n117,q117)&IF(p118,n118,q118)&IF(p119,n119,q119)&IF(p120,n120,q120)&IF(p121,n121,q121)&IF(p122,n122,q122)&IF(p123,n123,q123)&IF(p124,n124,q124)&IF(p125,n125,q125)&IF(p126,n126,q126)&IF(p127,n127,q127)&IF(p128,n128,q128)&IF(p129,n129,q129)&IF(p130,n130,q130)&IF(p131,n131,q131)&IF(p132,n132,q132)&IF(p133,n133,q133)&IF(p134,n134,q134)&IF(p135,n135,q135)&IF(p136,n136,q136)&IF(p137,n137,q137)&IF(p138,n138,q138)&IF(p139,n139,q139)&IF(p140,n140,q140)&IF(p141,n141,q141)&IF(p142,n142,q142)&IF(p143,n143,q143)&IF(p144,n144,q144)&IF(p145,n145,q145)&IF(p146,n146,q146)&IF(p147,n147,q147)&IF(p148,n148,q148)&IF(p149,n149,q149)&IF(p150,n150,q150)&IF(p151,n151,q151)&IF(p152,n152,q152)&IF(p153,n153,q153)&IF(p154,n154,q154)&IF(p155,n155,q155)&IF(p156,n156,q156)&IF(p157,n157,q157)&IF(p158,n158,q158)&IF(p159,n159,q159)&IF(p160,n160,q160)&IF(p161,n161,q161)&IF(p162,n162,q162)&IF(p163,n163,q163)&IF(p164,n164,q164)&IF(p165,n165,q165)&IF(p166,n166,q166)&IF(p167,n167,q167)&IF(p168,n168,q168)&IF(p169,n169,q169)&IF(p170,n170,q170)&IF(p171,n171,q171)&IF(p172,n172,q172)&IF(p173,n173,q173)&IF(p174,n174,q174)&IF(p175,n175,q175)&IF(p176,n176,q176)&IF(p177,n177,q177)&IF(p178,n178,q178)&IF(p179,n179,q179)&IF(p180,n180,q180)&IF(p181,n181,q181)&IF(p182,n182,q182)&IF(p183,n183,q183)&IF(p184,n184,q184)&IF(p185,n185,q185)&IF(p186,n186,q186)&IF(p187,n187,q187)&IF(p188,n188,q188)&IF(p189,n189,q189)&IF(p190,n190,q190)&IF(p191,n191,q191)&IF(p192,n192,q192)&IF(p193,n193,q193)&IF(p194,n194,q194)&IF(p195,n195,q195)&IF(p196,n196,q196)&IF(p197,n197,q197)&IF(p198,n198,q198)&IF(p199,n199,q199)&IF(p200,n200,q200)&IF(p201,n201,q201)&IF(p202,n202,q202)&IF(p203,n203,q203)&IF(p204,n204,q204)&IF(p205,n205,q205)&IF(p206,n206,q206)&IF(p207,n207,q207)&IF(p208,n208,q208)&IF(p209,n209,q209)&IF(p210,n210,q210)&IF(p211,n211,q211)&IF(p212,n212,q212)&IF(p213,n213,q213)&IF(p214,n214,q214)&IF(p215,n215,q215)&IF(p216,n216,q216)&IF(p217,n217,q217)&IF(p218,n218,q218)&IF(p219,n219,q219)&IF(p220,n220,q220)&IF(p221,n221,q221)&IF(p222,n222,q222)&IF(p223,n223,q223)&IF(p224,n224,q224)&IF(p225,n225,q225)&IF(p226,n226,q226)&IF(p227,n227,q227)&IF(p228,n228,q228)&IF(p229,n229,q229)&IF(p230,n230,q230)&IF(p231,n231,q231)&IF(p232,n232,q232)&IF(p233,n233,q233)&IF(p234,n234,q234)&IF(p235,n235,q235)&IF(p236,n236,q236)&IF(p237,n237,q237)&IF(p238,n238,q238)&IF(p239,n239,q239)&IF(p240,n240,q240)&IF(p241,n241,q241)&IF(p242,n242,q242)&IF(p243,n243,q243)&IF(p244,n244,q244)&IF(p245,n245,q245)&IF(p246,n246,q246)&IF(p247,n247,q247)&IF(p248,n248,q248)&IF(p249,n249,q249)&IF(p250,n250,q250)&IF(p251,n251,q251)&IF(p252,n252,q252)&IF(p253,n253,q253)&IF(p254,n254,q254)&IF(p255,n255,q255)&IF(p256,n256,q256)&IF(p257,n257,q257)&IF(p258,n258,q258)&IF(p259,n259,q259)&IF(p260,n260,q260)&IF(p261,n261,q261)&IF(p262,n262,q262)&IF(p263,n263,q263)&IF(p264,n264,q264)&IF(p265,n265,q265)&IF(p266,n266,q266)&IF(p267,n267,q267)&IF(p268,n268,q268)&IF(p269,n269,q269)&IF(p270,n270,q270)&IF(p271,n271,q271)&IF(p272,n272,q272)&IF(p273,n273,q273)&IF(p274,n274,q274)&IF(p275,n275,q275)&IF(p276,n276,q276)&IF(p277,n277,q277)&IF(p278,n278,q278)&IF(p279,n279,q279)&IF(p280,n280,q280)&IF(p281,n281,q281)&IF(p282,n282,q282)&IF(p283,n283,q283)&IF(p284,n284,q284)&IF(p285,n285,q285)&IF(p286,n286,q286)&IF(p287,n287,q287)&IF(p288,n288,q288)&IF(p289,n289,q289)&IF(p290,n290,q290)&IF(p291,n291,q291)&IF(p292,n292,q292)&IF(p293,n293,q293)&IF(p294,n294,q294)&IF(p295,n295,q295)&IF(p296,n296,q296)&IF(p297,n297,q297)&IF(p298,n298,q298)&IF(p299,n299,q299)&IF(p300,n300,q300)&IF(p301,n301,q301)&IF(p302,n302,q302)&IF(p303,n303,q303)&IF(p304,n304,q304)&IF(p305,n305,q305)&IF(p306,n306,q306)&IF(p307,n307,q307)&IF(p308,n308,q308)&IF(p309,n309,q309)&IF(p310,n310,q310)&IF(p311,n311,q311)&IF(p312,n312,q312)&IF(p313,n313,q313)&IF(p314,n314,q314)&IF(p315,n315,q315)&IF(p316,n316,q316)&IF(p317,n317,q317)&IF(p318,n318,q318)&IF(p319,n319,q319)&IF(p320,n320,q320)&IF(p321,n321,q321)&IF(p322,n322,q322)&IF(p323,n323,q323)&IF(p324,n324,q324)&IF(p325,n325,q325)&IF(p326,n326,q326)&IF(p327,n327,q327)&IF(p328,n328,q328)&IF(p329,n329,q329)&IF(p330,n330,q330)&IF(p331,n331,q331)&IF(p332,n332,q332)&IF(p333,n333,q333)&IF(p334,n334,q334)&IF(p335,n335,q335)&IF(p336,n336,q336)&IF(p337,n337,q337)&IF(p338,n338,q338)&IF(p339,n339,q339)&IF(p340,n340,q340)&IF(p341,n341,q341)&IF(p342,n342,q342)&IF(p343,n343,q343)&IF(p344,n344,q344)&IF(p345,n345,q345)&IF(p346,n346,q346)&IF(p347,n347,q347)&IF(p348,n348,q348)&IF(p349,n349,q349)&IF(p350,n350,q350)&IF(p351,n351,q351)&IF(p352,n352,q352)&IF(p353,n353,q353)&IF(p354,n354,q354)&IF(p355,n355,q355)&IF(p356,n356,q356)&IF(p357,n357,q357)&IF(p358,n358,q358)&IF(p359,n359,q359)&IF(p360,n360,q360)&IF(p361,n361,q361)&IF(p362,n362,q362)&IF(p363,n363,q363)&IF(p364,n364,q364)&IF(p365,n365,q365)&IF(p366,n366,q366)&IF(p367,n367,q367)&IF(p368,n368,q368)&IF(p369,n369,q369)&IF(p370,n370,q370)&IF(p371,n371,q371)&IF(p372,n372,q372)&IF(p373,n373,q373)&IF(p374,n374,q374)&IF(p375,n375,q375)&IF(p376,n376,q376)&IF(p377,n377,q377)&IF(p378,n378,q378)&IF(p379,n379,q379)&IF(p380,n380,q380)&IF(p381,n381,q381)&IF(p382,n382,q382)&IF(p383,n383,q383)&IF(p384,n384,q384)&IF(p385,n385,q385)&IF(p386,n386,q386)&IF(p387,n387,q387)&IF(p388,n388,q388)&IF(p389,n389,q389)&IF(p390,n390,q390)&IF(p391,n391,q391)&IF(p392,n392,q392)&IF(p393,n393,q393)&IF(p394,n394,q394)&IF(p395,n395,q395)&IF(p396,n396,q396)&IF(p397,n397,q397)&IF(p398,n398,q398)&IF(p399,n399,q399)&IF(p400,n400,q400)&IF(p401,n401,q401)&IF(p402,n402,q402)&IF(p403,n403,q403)&IF(p404,n404,q404)&IF(p405,n405,q405)&IF(p406,n406,q406)&IF(p407,n407,q407)&IF(p408,n408,q408)&IF(p409,n409,q409)&IF(p410,n410,q410)&IF(p411,n411,q411)&IF(p412,n412,q412)&IF(p413,n413,q413)&IF(p414,n414,q414)&IF(p415,n415,q415)&IF(p416,n416,q416)&IF(p417,n417,q417)&IF(p418,n418,q418)&IF(p419,n419,q419)&IF(p420,n420,q420)&IF(p421,n421,q421)&IF(p422,n422,q422)&IF(p423,n423,q423)&IF(p424,n424,q424)&IF(p425,n425,q425)&IF(p426,n426,q426)&IF(p427,n427,q427)&IF(p428,n428,q428)&IF(p429,n429,q429)&IF(p430,n430,q430)&IF(p431,n431,q431)&IF(p432,n432,q432)&IF(p433,n433,q433)&IF(p434,n434,q434)&IF(p435,n435,q435)&IF(p436,n436,q436)&IF(p437,n437,q437)&IF(p438,n438,q438)&IF(p439,n439,q439)&IF(p440,n440,q440)&IF(p441,n441,q441)&IF(p442,n442,q442)&IF(p443,n443,q443)&IF(p444,n444,q444)&IF(p445,n445,q445)&IF(p446,n446,q446)&IF(p447,n447,q447)&IF(p448,n448,q448)&IF(p449,n449,q449)&IF(p450,n450,q450)&IF(p451,n451,q451)&IF(p452,n452,q452)&IF(p453,n453,q453)&IF(p454,n454,q454)&IF(p455,n455,q455)&IF(p456,n456,q456)&IF(p457,n457,q457)&IF(p458,n458,q458)&IF(p459,n459,q459)&IF(p460,n460,q460)&IF(p461,n461,q461)&IF(p462,n462,q462)&IF(p463,n463,q463)&IF(p464,n464,q464)&IF(p465,n465,q465)&IF(p466,n466,q466)&IF(p467,n467,q467)&IF(p468,n468,q468)&IF(p469,n469,q469)&IF(p470,n470,q470)&IF(p471,n471,q471)&IF(p472,n472,q472)&IF(p473,n473,q473)&IF(p474,n474,q474)&IF(p475,n475,q475)&IF(p476,n476,q476)&IF(p477,n477,q477)&IF(p478,n478,q478)&IF(p479,n479,q479)&IF(p480,n480,q480)&IF(p481,n481,q481)&IF(p482,n482,q482)&IF(p483,n483,q483)&IF(p484,n484,q484)&IF(p485,n485,q485)&IF(p486,n486,q486)&IF(p487,n487,q487)&IF(p488,n488,q488)&IF(p489,n489,q489)&IF(p490,n490,q490)&IF(p491,n491,q491)&IF(p492,n492,q492)&IF(p493,n493,q493)&IF(p494,n494,q494)&IF(p495,n495,q495)&IF(p496,n496,q496)&IF(p497,n497,q497)&IF(p498,n498,q498)&IF(p499,n499,q499)&IF(p500,n500,q500)&IF(p501,n501,q501)&IF(p502,n502,q502)&IF(p503,n503,q503)&IF(p504,n504,q504)&IF(p505,n505,q505)&IF(p506,n506,q506)&IF(p507,n507,q507)&IF(p508,n508,q508)&IF(p509,n509,q509)&IF(p510,n510,q510)&IF(p511,n511,q511)&IF(p512,n512,q512)&IF(p513,n513,q513)&IF(p514,n514,q514)&IF(p515,n515,q515)&IF(p516,n516,q516)&IF(p517,n517,q517)&IF(p518,n518,q518)&IF(p519,n519,q519)&IF(p520,n520,q520)&IF(p521,n521,q521)&IF(p522,n522,q522)&IF(p523,n523,q523)&IF(p524,n524,q524)&IF(p525,n525,q525)&IF(p526,n526,q526)&IF(p527,n527,q527)&IF(p528,n528,q528)&IF(p529,n529,q529)&IF(p530,n530,q530)&IF(p531,n531,q531)&IF(p532,n532,q532)&IF(p533,n533,q533)&IF(p534,n534,q534)&IF(p535,n535,q535)&IF(p536,n536,q536)&IF(p537,n537,q537)&IF(p538,n538,q538)&IF(p539,n539,q539)&IF(p540,n540,q540)&IF(p541,n541,q541)&IF(p542,n542,q542)&IF(p543,n543,q543)&IF(p544,n544,q544)&IF(p545,n545,q545)&IF(p546,n546,q546)&IF(p547,n547,q547)&IF(p548,n548,q548)&IF(p549,n549,q549)&IF(p550,n550,q550)&IF(p551,n551,q551)&IF(p552,n552,q552)&IF(p553,n553,q553)&IF(p554,n554,q554&IF(p555,n555,q555)&IF(p556,n556,q556)&IF(p557,n557,q557)&IF(p558,n558,q558)&IF(p559,n559,q559)&IF(p560,n560,q560)&IF(p561,n561,q561)&IF(p562,n562,q562)&IF(p563,n563,q563)&IF(p564,n564,q564)&IF(p565,n565,q565)&IF(p566,n566,q566)&IF(p567,n567,q567)&IF(p568,n568,q568)&IF(p569,n569,q569)&IF(p570,n570,q570)&IF(p571,n571,q571)&IF(p572,n572,q572)&IF(p573,n573,q573)&IF(p574,n574,q574)&IF(p575,n575,q575)&IF(p576,n576,q576)&IF(p577,n577,q577)&IF(p578,n578,q578)&IF(p579,n579,q579)&IF(p580,n580,q580)&IF(p581,n581,q581)&IF(p582,n582,q582)&IF(p583,n583,q583)&IF(p584,n584,q584)&IF(p585,n585,q585)&IF(p586,n586,q586)&IF(p587,n587,q587)&IF(p588,n588,q588)&IF(p589,n589,q589)&IF(p590,n590,q590)&IF(p591,n591,q591)&IF(p592,n592,q592)&IF(p593,n593,q593)&IF(p594,n594,q594)&IF(p595,n595,q595)&IF(p596,n596,q596)&IF(p597,n597,q597)&IF(p598,n598,q598)&IF(p599,n599,q599)&IF(p600,n600,q600)&IF(p601,n601,q601)&IF(p602,n602,q602)&IF(p603,n603,q603)&IF(p604,n604,q604)&IF(p605,n605,q605)&IF(p606,n606,q606)&IF(p607,n607,q607)&IF(p608,n608,q608)&IF(p609,n609,q609)&IF(p610,n610,q610)&IF(p611,n611,q611)&IF(p612,n612,q612)&IF(p613,n613,q613)&IF(p614,n614,q614)&IF(p615,n615,q615)&IF(p616,n616,q616)&IF(p617,n617,q617)&IF(p618,n618,q618)&IF(p619,n619,q619)&IF(p620,n620,q620)&IF(p621,n621,q621)&IF(p622,n622,q622)&IF(p623,n623,q623)&IF(p624,n624,q624)&IF(p625,n625,q625)&IF(p626,n626,q626)

Thanks!

Can you upload an example workbook

I cannot upload it because the file is too big even if I zip it. I will explain in words what the purpose of my formula is. I have a list of words (in table format) that I want to select from. I could potentially have 1-4 selections from the list, but each time the selections will be different- therefore the need for a 600-some long list. I want the selection (based on TRUE or FALSE) to appear in the cell with the formula is in. I'm not looking to solve a mathematical equation, only to transfer a selection from the table to another cell in the workbook. The but formula is too big.

Is that correct, P22 will contain TRUE or FALSE? Not some other value?

What are the values of N22 and Q22 in this part of the formula?

P22 Contains TRUE or FALSE. If P22 contains TRUE then N22 appears which is text. If P22 contains FALSE then Q22 appears which is a blank cell. This is similar for each part of the equation.

There are 605 IF statements. Are you expecting a concatenation of 605 words/characters/numbers? or are many of the cells blank?

Though there would be the option of 605 words appearing in this formula, only a few would be selected at a time.

@Chemist B- I don't how how to put that into a module. How would I do that. I'm really sorry I have tried condensing my workbook as much as I can, but I can't get it to be small enough to attach.

Try transferring your data to this sheet

function1.xlsm

and using

Please Login or Register  to view this content.

as your function.

or

Please Login or Register  to view this content.

If you want it to be volatile (constantly updating)

(this uses special K's method)

I'm a little confused about what I'm supposed to do. Can you explain?

Awesome that works... only one question. Is it possible to make it go down a column instead of across a row?

Yes

=IFERROR(INDEX($N$22:$N$626,SMALL(IF($P$22:$P$626=TRUE,ROW($A$22:$A$626)-ROW($A$22)+1,""),ROWS($A$1:A1))),"")

should do it

Enter as before (CTRL+SHIFT+ENTER) and drag down

Thank you so much!

If your problem has been solved could you please mark the thread as such("Thread Tools" at top of fist post).

Thank you.