I have a somewhat complex problem, so I will try to explain with enough
detail what I have here without writing a book about it.

I am adapting a large spreadsheet that was not created in excel. Many of the
functions work a little differently, so I was not able to simply export and
have it work correctly. I have no background in VBA and would prefer to avoid
it, as it would take a great deal of debugging, and the purpose of my problem
is only datacentric. There do not have to be any moving parts, just data
compiled from the workbook and displayed back in one cell.

I have managed to adapt almost everything except this one function:

What this does is takes a long string of text stored in one cell that acts
as template to populate unique values from another sheet creating one long
string of text that is unique to a row of data from another sheet.

The "import values" are stored in a range just above the template cell, and
those values are represented in the template text inside brackets. For
example, one variable I will "import" is Name. The word Name is entered in
one of the cells in the range above the template cell, lets say A1. The text
of the template cell contains {Name}.

Like:

blahblahblahtexttexttext{Name}blahblahblah

This formula currently reports back the template cell, but substitutes in
the template cell, the value contained in A1, with the value contained at an
address determined by references next to the cell that contains this formula.

Here is my problem. I am needing to substitute a lot of values, and the
formula is waaay to long. Here is what it looks like, and while it would
work, excel will not accept something this long:


IFERROR(if(F19,IF(backwards_compatible,"<descripti on<![CDATA["&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBS TITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE (SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(S UBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTIT UTE(SUBSTITUTE(INDIRECT(INDIRECT("Template_"&G19&" _rn")&"!E86"),indirect(I19&"!$F$50"),indirect("Pla cemarkData!I"&D19)),indirect(I19&"!$F$51"),indirec t("PlacemarkData!J"&D19)),indirect(I19&"!$F$52"),i ndirect("PlacemarkData!K"&D19)),indirect(I19&"!$F$ 53"),indirect("PlacemarkData!L"&D19)),indirect(I19 &"!$F$54"),indirect("PlacemarkData!M"&D19)),indire ct(I19&"!$F$55"),indirect("PlacemarkData!N"&D19)), indirect(I19&"!$F$56"),indirect("PlacemarkData!O"& D19)),indirect(I19&"!$F$57"),indirect("PlacemarkDa ta!P"&D19)),indirect(I19&"!$F$58"),indirect("Place markData!Q"&D19)),indirect(I19&"!$F$59"),indirect( "PlacemarkData!R"&D19)),indirect(I19&"!$F$60"),ind irect("PlacemarkData!S"&D19)),indirect(I19&"!$F$61 "),indirect("PlacemarkData!T"&D19)),indirect(I19&" !$F$62"),indirect("PlacemarkData!U"&D19)),indirect (I19&"!$F$63"),indirect("PlacemarkData!V"&D19)),in direct(I19&"!$F$64"),indirect("PlacemarkData!W"&D1 9)),indirect(I19&"!$F$65"),indirect("PlacemarkData !X"&D19)),indirect(I19&"!$F$66"),indirect("Placema rkData!Y"&D19)),indirect(I19&"!$F$67"),indirect("P lacemarkData!Z"&D19)),indirect(I19&"!$F$68"),indir ect("PlacemarkData!AA"&D19)),indirect(I19&"!$F$69" ),indirect("PlacemarkData!AB"&D19)),indirect(I19&" !$F$70"),indirect("PlacemarkData!AC"&D19)),indirec t(I19&"!$F$71"),indirect("PlacemarkData!AD"&D19)), indirect(I19&"!$F$72"),indirect("PlacemarkData!AE" &D19)),indirect(I19&"!$F$73"),indirect("PlacemarkD ata!AF"&D19)),indirect(I19&"!$F$74"),indirect("Pla cemarkData!AG"&D19)),indirect(I19&"!$F$75"),indire ct("PlacemarkData!AH"&D19)),indirect(I19&"!$F$76") ,indirect("PlacemarkData!AI"&D19)),indirect(I19&"! $F$77"),indirect("PlacemarkData!AJ"&D19)),indirect (I19&"!$F$78"),indirect("PlacemarkData!AK"&D19)),i ndirect(I19&"!$F$79"),indirect("PlacemarkData!AL"& D19)),indirect(I19&"!$F$80"),indirect("PlacemarkDa ta!AM"&D19)),indirect(I19&"!$F$81"),indirect("Plac emarkData!AN"&D19)),CHAR(10),"")&"]]</description","<description<![CDATA["&upgrade_msg_maps&"]]</description"),""),error_msg_prefix&ADDRESS(D19,CO LUMN(),4)&error_msg_suffix)


The Iferror is one of the symantics I had to fix in the rest of the
workbook, I can take care of that, but first I need to figure out a way to
organize the data so that I can fit it in a formula.

Is this enough info? I can email a copy of the whole document to someone who
can help me think through this.