2

Desktop software can use a number of formulas to determine an address for a named range. Google's not there yet.

Given that I have the address since the formula works in a regular cell.

Is there a way I can use it for a named range without indirect?

Say cell formula in A1 results as "C16" and cell formula in A2 results as "C150". I need to take that and make a named range =C16:C150. Resulting in a dynamic range as A1 and A2 change. The mentioned formula is just a complex INDEX(MATCH wrapped in a CELL("ADDRESS"..)

Improve this question
1
  • maybe this similar issue could help in future: webapps.stackexchange.com/questions/120038/… in short: it allows to import cells dynamically without the usage of INDIRECT or ADDRESS and with the ability to fetch data regardless of deleting or adding new rows and columns*
    – user0
    Sep 7, 2018 at 16:30

1 Answer 1

Reset to default
2

Put =A1&":"&A2 in a cell (say G20).

Name G20 as your range "MyRange".

Use Indirect(MyRange) to reference the dynamic range.

This is the only way to have your dynamic range as a named range in Google Sheets.

If you want to not use indirect (if I understand you correctly), you could use row() and column() functions in conjunction with Address(). In that example you would need three cells, one for the column and two for the row numbers (unless the column is always the same).

An example formula would then be to simply wrap the formula that gives you the address in A1 / A2 with row() or column():

=row(complex INDEX(MATCH wrapped in a CELL("ADDRESS"..))
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.