Address function referencing a range

Hey guys,

So i have a range in one cell in the style R1C1, and i want to reference that one cell and sum or apply that range to any formular using indirect address, but i can't get it to work.

the address formula is =ADDRESS(8,O4,1,1)&":"&ADDRESS($M$1,O4,1,1) and the result returns $O$8:$O$161. This is the range i now want to use.

I've been trying to reference it by doing this in my wanted formula (whether its sum, or counta, or a range for other formulas) =(INDIRECT(ADDRESS(6,15,1,1),TRUE)) - but it doesn't work! (even tho it returns the string $O$8:$O$161)

Attached exmaple,

The problem is that there may be more or less than 26 rows. there could 500 or 1,

However i figured a way around it. see attachement

What i'm really trying to do is place the range into a formula to remove blanks. in the formula there is two ranges M$9:M$806, Q$9:Q$806. The first is the column to remove blanks, and the second is the column to paste the result. I need this dynamic, I still get issues however

=IF(ROW()-ROW(Q$9:Q$806)+1>ROWS(M$9:M$806)-COUNTBLANK(M$9:M$806),"",INDIRECT(ADDRESS(SMALL((IF(M$9:M$806<>"",ROW(M$9:M$806),ROW()+ROWS(M$9:M$806))),ROW()-ROW(Q$9:Q$806)+1),COLUMN(M$9:M$806),4)))