Insert > Name > Define

[Chris Johnson]

Why does this work for a Name definition of a range?

=Sheet1!$A$3:INDEX(DataforCharts!$A:$A,MATCH(9.99999E+307,DataforCharts!$A:$A))

It works but I can't figure out why. The INDEX returns the value of the
last cell with data in it, however if I just replace all of that with the
value it does not work anymore. For example this does NOT work.

=Sheet1!$A$3:40857

 

[Dave Peterson]

Actually, this portion:

=MATCH(9.99999E+307,DataforCharts!$A:$A)

returns the last row in column A of the DataForCharts worksheet that has numeric
data in it.


But you didn't translate the =index() portion. That would include the column
reference, too.
Essentially:

=Sheet1!$A$3:A40857