How do I write an Excel 2010 formula that pulls a value that can change location?

How do I write a formula that pulls the value of a cell at the bottom of a list of cells, even when the list increases/decreases in size. In VBA, I would use soomething like Range("A1").end(xlDown).Value, but I want to do this without using VBA.
shampouyaAsked:
Who is Participating?
 
Tj aCommented:
To refer to the last cell in the range use the INDEX formula as follows,
If your range is called "experts"

=INDEX(experts, Row number, Column number)
0
 
Martin LissOlder than dirtCommented:
Create a Named Range for the cell and refer to the Named Range in the formula.
0
 
shampouyaAuthor Commented:
Ok just created the named range, how do I refer to the final cell within that?
0
 
Tj aCommented:
To provide the steps to what Martin Liss commented,

Highlight the cells,
right click the range,
Click Define Name,
In the box that pops up, define the cell name
press enter

Now include that name in your formulas.
0
 
CompProbSolvCommented:
If you can be sure that there are no empty cells (potentially a big "if"), try this:
http://www.techrepublic.com/blog/microsoft-office/return-the-last-item-in-an-excel-column/

This has you use
=INDEX(column, COUNTA(column), 1)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.