Hi, I've been having a headache since yesterday trying to come up with a solution to my problem.
I created an array formula based on a complicated factor we have on our Spreadsheet. You actually don't have to understand the whole formula,
but rather just the part where is going wrong, as shown below:
{=INDEX(INDIRECT(LEFT(ADDRESS(ROW(A530);168+MATCH(I530;$FM$2:$RN$2;0));3)&"$2:"&LEFT(ADDRESS(ROW(A530);200+MATCH(I530;$FM$2:$RN$2;0));3)&"$2");MATCH(TRUE;ISBLANK(INDIRECT(ADDRESS(530;168+MATCH(I530;$FM$2:$RN$2;0))&":"&ADDRESS(530;200+MATCH(I530;$FM$2:$RN$2;0))));0))-1}
The Green is doing it's thing right, but I need to change the Red.
The way it's written above is returning no error, but I need to "pull" the formula down, and the "530" needs to become 531,532...and so on.
If I use "ROW(A530)" for Instance, it returns an #N/A error, and the Error Evaluation shows that after all the formulas inside the Address are made,
when it comes to the Address, it shows like this:
"INDIRECT("$HC$530:"&ADDRESS({530};243))"
And then:
"INDIRECT("$HC$530:"&{"$II$530"})"
= #N/A
Does someone have any idea how the first half of the formula didn't bring the brackets, and the second half brought this annoying guys => "{}" ?
Thanks in advance, John.
Bookmarks