How do I link a cell in Excel to the last row of data in a worksheet?

Question

I'm trying to make a spreadsheet that ties to a data entry worksheet. I would like this particular cell to only pull the most recent data entered(a cell in the the last entered row). Does anyone know of a way to do that? Thanks

Answer 1

The easiest approach would be to have new data entered at the top of the table in the data entry worksheet and then use absolute row referencing in the other sheet to always reference the first row.

Alternatively:

Row number of last used row in a column:
=SUMPRODUCT(MAX((ROW(A1:A1000))*(A1:A1000<>"")))
Row number of the last numerical value within column C...
=MATCH(9.99999999999999E+307,C:C)

Answer 2

At the bottom of the spreadsheet, you should see sheet tabs (Sheet1, Sheet2, Sheet3). Right click on the current tab and select Move or Copy. Select New Book from the dropdown menu and check the Create a copy box. This will copy the entire sheet into the new book, including all the formulas and formatting.

Answer 3

No NO No
it goes like this
If your column (You want the last cell of it is B) then in the cell you want to get the last value, paste this

=OFFSET( B1, COUNTA( B:B)-1,0)

assuming that your column B is full starting from cell B1

Enjoy my profile, I am the VBAXLMan

Answer 4

=OFFSET( B1, COUNTA( B:B)-1,0) will do the trick but will introduce subtle errors. Many users have the habit of inserting empty rows. Think of more foolproof solution instead.