How to define a "from this row to the bottom of the spreadsheet"range?

[Ney André de Mello Zunino]

Hello.

In a spreadsheet, I have a formula which refers to the values of a
column ('C', for instance). The first line of that column is a title, so
I currently use a range that begins with 'C2'. There is no total at the
bottom of the column. All cells that follow the last value are blank.

The problem is that the number of values on that column grows
periodically. As of now, I am using an arbitrary high value to make up
the range in the formula, e.g. 'C2:C1000'. The question is: how can I
specify that range without using a magic number? In other words, how
does one define a range that means: from this row to the bottommost row.

Thank you,

 

[Gord Dibben]

Perhaps a Dynamic Range would suit your purposes.

It expands and contracts with the change of entries.

See Debra Dalgleish's site for instruction on creating Dynamic Ranges and naming
them

http://www.contextures.on.ca/xlNames01.html#Dynamic


Gord Dibben MS Excel MVP

 

[Harlan Grove]

Ney André de Mello Zunino wrote...
....

specify that range without using a magic number? In other words, how
does one define a range that means: from this row to the bottommost row.

....

You could define a name like C.HD ([col] C here down) referring to the
formula

=INDEX($C:$C,N(ROW())):INDEX($C:$C,ROWS($C:$C))

 

[Harlan Grove]

Harlan Grove wrote...
....

=INDEX($C:$C,N(ROW())):INDEX($C:$C,ROWS($C:$C))

The N call was unnecessary. Simplify it to

=INDEX($C:$C,ROW()):INDEX($C:$C,ROWS($C:$C))