Google Spreadsheet range names

Question

In Google Docs Spreadsheets, one can use Range Names to put labels on ranges of cells to make formulas more legible. In most formulas, one can use the range C:C to denote the entire C column, and C2:C to denote the entire C column after and including C2.

Is there a way to create range names of the same nature? When I try C:C or C2:C or Sheet!C:C or 'Sheet'!C:C I always get the error "The range you specified is not in a valid range format." I would like the range name to expand as my form adds rows to my spreadsheet. Thanks.

Bricklayer

Answer 1

I don't think so... even if you select a column manually while in the Range Name selector, it complains. That would be a nice feature and it would make sense since they support column ranges for formulas already.

Montmartre

Answer 2

I just discovered the if you use the '-' operator, it starts from the bottom row. So,

=INDIRECT("-D:D12")

starts from the last row and works it's way up to D12!

Travis

Not to my knowledge. I found this out by tinkering. – Travis

Answer 3

I had a similar problem. Although I do not know how to do exactly what you are asking, you can do essentially the same thing by referencing cells that are not yet created.

For example: Column C currently has 100 cells (100 rows in the sheet) Instead of referencing it with C:C, use C1:C999

If you make the row reference high enough, then you can account for future rows that you will create. Hope it helps.

Cipher

Good find Nathan, thanks! Unfortunately what you suggest still isn't possible when creating a named range ("Invalid Range") – Bricklayer

Answer 4

I had the same problem with ranges such as A3:A which normally work in other places such as ARRAYFORMULA(), but the workaround is to not specify the starting row, such as A:A. In cases when this would be a problem, you can proxy the data through another column using something like ARRAYFORUMULA(A25:A) as the formula.

Update: Apparently I haven't read the question properly. I see that the OP had tried leaving out the row number, so perhaps it wasn't working at that time, but it does now. The notations still don't work.

Update2: I didn't notice that google spreadsheet replaces ranges like A:A to A1:A50, so new rows added later on do not still get included. That I think is what @Dean is trying to say in his answer.

Foumart

Answer 5

I believe this does work now. I have a range name of "Sheet1!A10:AW10" with no problems.

If you try to do a whole column, I think it will just take all the available cells in the column at that time. i.e. if you make more cells later, you need to manually add to the range name.

Echopraxia

Your example was not relevant. It does not work as you also stated later that ranges are not yet dynamically expanding. – Snatch

Answer 6

I think it's a helpful tool to use Insert -> Define new range to make a wizard appear and make the syntax correct. Hehe

My response in other topic

Marianamariand