Google sheets; Arbitrary range as string

Question

How to convert a range of arbitrary size, to it's string representation, to be later used using INDIRECT ?

ADDRESS only works for a single cell.

No scripting please :)

Only formulas.

EDIT 1 ; Also assume the range is computed through a complex formula. So no cell addresses are available.

EDIT 2 ;

=ArrayFormula(IF((OFFSET(INDIRECT(PayStaff),0,10)>PayrollStart)*(OFFSET(INDIRECT(PayStaff),0,10)<=PayrollEnd),(OFFSET(INDIRECT(PayStaff),0,10)-PayrollStart),0)+IF((OFFSET(INDIRECT(PayStaff),0,11)>=PayrollStart)*(OFFSET(INDIRECT(PayStaff),0,11)<PayrollEnd),(PayrollEnd-OFFSET(INDIRECT(PayStaff),0,11)),0))

No sample sheet as this is a hypothetical type question.

Above is an actual formula I'm using, one of many in my efforts at fully automation. At any time, the range string in "PayStaff" can change, coupled with repeated but identical OFFSET calls, soon makes the formula unreadable. One of my ideas to solve the readability is to get the range-string of (eg: OFFSET(INDIRECT(PayStaff),0,10) ) and reuse it, shortening the formula and increasing readability. Also note, the example does NOT have size increase of range which I require as well.

But lets suppose that PayStaff = "A1:A10", where the number of rows can vary. Considering that OFFSET has 4 parameters, how to get the resultant range as a string? Is this possible?

Answer 1

This would give you the start of the range as a string:

=cell("Address",OFFSET(INDIRECT(PayStaff),0,10))

The end of the range is more awkward - you would have to add the number of rows in the range (-1) to the row offset:

=cell("Address",OFFSET(INDIRECT(PayStaff),rows(indirect(PayStaff))-1,10))