So I've been at this for the last few days.
I've attached the same spreadsheet, but made some adjustments to the formula. I'm trying to make the formula as dynamic as possible.
So in cell E5, I was able to create an address array, but it's not searchable. In cell E6, I manually created the address array to see if the concept would work where a text matches one of the address arrays. It works, but I can't get a value from the searchable address when using the INDIRECT function. I think it's because the values spilled to the next cells.
Can anyone help with cell E5 and get the dynamic address array to be a valid array. I'm assuming the formula is evaluating it as a text and not as an array. Then in cell E6, can we get 1 result in the same cell and then use an INDIRECT function on it, so it can evaulate text to a value? Thanks.
In the end I'm trying to do a =SUM((previous workout weight)+(long formula that evaluates to a value dynamically))
Edit: Updated spreadsheet. I was able to figure out how to extract an address in E5:
Code:
=MID(FORMULATEXT(D5),SEARCH(TEXTSPLIT(TEXTJOIN(",",TRUE,ADDRESS(ROW(E2:I2),COLUMN(E2:I2))),","),FORMULATEXT(D5)),4)