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?

This is not possible as the range is computed through a complex formula (OFFSETs with variable number of columns/rows). So no cell addresses are available. Updated OP to indicate as such. — Riyaz Mansoor
I think the function you want is CELL("ADDRESS"...), but we do need to see the formula to be able to answer the question. Also if you are asking for the range as a string, you may be asking the wrong question. — Tom Sharpe

Legion Legion · Accepted Answer · 2020-03-20T17:47:19

I just used this to do something similar, hope this helps:

// <range here> = is the range you want to describe,
// can be output of some other formula but has to be a
// rectangle for this to work.
// Remove // comments and line breaks before pasting to sheet.

// concatenate top left corner with colon and bottom right corner.
=CONCATENATE(
  // use address to get top left corner
  ADDRESS(ROW(<range here>), COLUMN(<range here>)),
  ":",
  // use address to get bottom right corner
  ADDRESS(
    ROW(<range here>)+ROWS(<range here>)-1,
    COLUMN(<range here>)+COLUMNS(<range here>)-1
  )
)

Google sheets; Arbitrary range as string

2 Answers