Unable to set second argument of an array as a function

Hi all,

I am having issues with finding a way to customise an array on Excel.

I need to define the array from A85:A95. However, the A95 cell has to be retrieved from the following function:

"=SUBSTITUTE(ADDRESS(85,1,4),"85","")&(ROW(A85)+10)"

This formula currently returning "A95".

If I type into any cell "=A85:A95" I am able to see the results in a 10-row single-column list and I am trying to replace "A95" in the array by "=SUBSTITUTE(ADDRESS(85,1,4),"85","")&(ROW(A85)+10)" using:

"=A85: (SUBSTITUTE(ADDRESS(85,1,4),"85","")&(ROW(A85)+10))"

In this case, Excel is unable to read the array and I do not know why. Can anyone please help me out?


Note:

Most of the values here are representative only as this is a very simplified version of the original formulas.

Eventually, I want to refer to this array from a different worksheet if that is possible.

Thanks.

How about

Formula:

Please Login or Register  to view this content.

Yes, that worked well.

What if this range of cells comes from a different worksheet?

I tried =Sheet1!A85:INDEX(A:A,ROW(COA!A85)+10) but it did not work.

Any ideas?

It would need to be

Formula:

Please Login or Register  to view this content.