I got an array formula (CSE) that works perfectly. It uses a cell reference as its source. Now I want to make my sheet clean by decreasing the amount of cells I use for my formulas by embedding all into one, but I'm getting an error instead. Help me out?
Formula that works:
{=IF(ISERROR(FIND(",";REPLACE(A2;1;LEN(LEFT(SUBSTITUTE(A2;",";", ");SMALL(FIND(CHAR(ROW(INDIRECT("65:90")));SUBSTITUTE(A2;",";", ")&"ABCDEFGHIJKLMNOPQRSTUVWXYZÅÄÖ");2)-1))-1;"")));LEFT(A2;FIND(",";A2)-1)&", "&REPLACE(A2;1;LEN(LEFT(SUBSTITUTE(A2;",";", ");SMALL(FIND(CHAR(ROW(INDIRECT("65:90")));SUBSTITUTE(A2;",";", ")&"ABCDEFGHIJKLMNOPQRSTUVWXYZÅÄÖ");2)-1))-1;"");LEFT(SUBSTITUTE(A2;",";", ");SMALL(FIND(CHAR(ROW(INDIRECT("65:90")));SUBSTITUTE(A2;",";", ")&"ABCDEFGHIJKLMNOPQRSTUVWXYZÅÄÖ");2)-1)&" "&MID(REPLACE(A2;1;LEN(LEFT(SUBSTITUTE(A2;",";", ");SMALL(FIND(CHAR(ROW(INDIRECT("65:90")));SUBSTITUTE(A2;",";", ")&"ABCDEFGHIJKLMNOPQRSTUVWXYZÅÄÖ");2)-1))-1;"");2;FIND(",";REPLACE(A2;1;LEN(LEFT(SUBSTITUTE(A2;",";", ");SMALL(FIND(CHAR(ROW(INDIRECT("65:90")));SUBSTITUTE(A2;",";", ")&"ABCDEFGHIJKLMNOPQRSTUVWXYZÅÄÖ");2)-1))-1;""))-2)&", "&MID(SUBSTITUTE(A2;",";", ");FIND(",";SUBSTITUTE(A2;",";", "))+2;LEN(SUBSTITUTE(A2;",";", "))-FIND(",";SUBSTITUTE(A2;",";", "))))}
I'm now replacing all occurences of "A2" with this formula:
OFFSET(INDIRECT("'Input'!"&INDIRECT("'Background'!D"&ROW()-5));0;-1)
And formula results in an error, why?
I checked Formula Evaluator and it starts giving me the #VALUE at INDIRECT("'Background'!D"&ROW()-5) (becomes Background!D1 = #VALUE), but i know its a real, existing cell because I use that formula in another cell.
Formula that works:
{=IF(ISERROR(FIND(",";REPLACE(A2;1;LEN(LEFT(SUBSTITUTE(A2;",";", ");SMALL(FIND(CHAR(ROW(INDIRECT("65:90")));SUBSTITUTE(A2;",";", ")&"ABCDEFGHIJKLMNOPQRSTUVWXYZÅÄÖ");2)-1))-1;"")));LEFT(A2;FIND(",";A2)-1)&", "&REPLACE(A2;1;LEN(LEFT(SUBSTITUTE(A2;",";", ");SMALL(FIND(CHAR(ROW(INDIRECT("65:90")));SUBSTITUTE(A2;",";", ")&"ABCDEFGHIJKLMNOPQRSTUVWXYZÅÄÖ");2)-1))-1;"");LEFT(SUBSTITUTE(A2;",";", ");SMALL(FIND(CHAR(ROW(INDIRECT("65:90")));SUBSTITUTE(A2;",";", ")&"ABCDEFGHIJKLMNOPQRSTUVWXYZÅÄÖ");2)-1)&" "&MID(REPLACE(A2;1;LEN(LEFT(SUBSTITUTE(A2;",";", ");SMALL(FIND(CHAR(ROW(INDIRECT("65:90")));SUBSTITUTE(A2;",";", ")&"ABCDEFGHIJKLMNOPQRSTUVWXYZÅÄÖ");2)-1))-1;"");2;FIND(",";REPLACE(A2;1;LEN(LEFT(SUBSTITUTE(A2;",";", ");SMALL(FIND(CHAR(ROW(INDIRECT("65:90")));SUBSTITUTE(A2;",";", ")&"ABCDEFGHIJKLMNOPQRSTUVWXYZÅÄÖ");2)-1))-1;""))-2)&", "&MID(SUBSTITUTE(A2;",";", ");FIND(",";SUBSTITUTE(A2;",";", "))+2;LEN(SUBSTITUTE(A2;",";", "))-FIND(",";SUBSTITUTE(A2;",";", "))))}
I'm now replacing all occurences of "A2" with this formula:
OFFSET(INDIRECT("'Input'!"&INDIRECT("'Background'!D"&ROW()-5));0;-1)
And formula results in an error, why?
I checked Formula Evaluator and it starts giving me the #VALUE at INDIRECT("'Background'!D"&ROW()-5) (becomes Background!D1 = #VALUE), but i know its a real, existing cell because I use that formula in another cell.