Referencing latest entry - Excel
Referencing latest entry - Excel
(OP)
This should be easy to do...but I seem to be forgetting how I guess. I have dataset where daily values are entered. I need to reference the last entry of my data to another cell. I want it to where when the data is entered, the new cell is updated automaticlly.
Thanks
Thanks





RE: Referencing latest entry - Excel
For instance if the last data is entered into cell A10 and your reference cell is A20 then go to A20 and enter =A10. When A10 changes so too should A20.
David Baird
mrbaird@hotmail.com
Sr Controls Engineer
EET degree.
Journeyman Electrician.
RE: Referencing latest entry - Excel
Thanks again
RE: Referencing latest entry - Excel
If you have a column with the time or date corresponding to each data entry, then you can easily search the time / date column to find the maximum value (i.e. the last data entry), and then use a VLOOKUP function to find the corresponding data point.
E.g. If your dates are in Column A, and your data is in Column B, the following should work:
=VLOOKUP(MAX(A1:A100),A1:B100,2,FALSE)
Using this method, your data entry points don't even need to be in date-sequential order - the function will find the data point corresponding to the latest time / date. The "FALSE" flag is required to find an exact match on the time or date - if you omit this term, the function may return an incorrect value based on an approximate match. (I have assumed 100 rows is more than enough to accommodate all of your projected data needs - increase the range to something bigger if required.)
Alternatively, a workable "kludge" is to set your "reference cell" to point to A30 (as in your example). When you need to add some more data, INSERT a new row above Row 30, copy the old data from what is now A31 into the inserted A30, and enter the new data into A31. The formula in your "reference cell" will have updated to point to A31. Clumsy, I know, but it might help.
RE: Referencing latest entry - Excel
Enter either the date or a sequential series of numbers in column A.
Enter your data in column B
In the cell that you want the latest data (bottom most data from column B) to show, enter the formula:
=hlookup(max(a1:a9999),a1:a9999,1)
RE: Referencing latest entry - Excel
"Great minds think alike."
Too bad I beat you to the punch!
(Note that when using VLOOKUP or HLOOKUP, I nearly always use the optional "FALSE" argument, to force an EXACT match - otherwise, you may get an erroneous approximate match.)
RE: Referencing latest entry - Excel
RE: Referencing latest entry - Excel
RE: Referencing latest entry - Excel
1. if your data has no "gaps", i.e. blank lines, then you can reference the last non-blank cell by =OFFSET(A1,COUNTA(A:A)-1,0)
2. if your data has gaps, and you want to find the last entry, then you need the array formula (press ctrl-enter after editing the formula):=INDIRECT(ADDRESS(MAX((A1:A100<>"")*ROW(A1:A100)),COLUMN(A1:A100)),4)
I assumed the data range to be in A1:A100, edit as needed.
Kudos to Chip Pearson for the formula in option 2.
Cheers,
Joerd
Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.
RE: Referencing latest entry - Excel
=INDEX(A1:A100,COUNTA(A1:A100))
RE: Referencing latest entry - Excel
If your data is numeric, then you can find the last entry in column A by using the following formula ...
=LOOKUP(9.9E+307,A:A)
what this is doing is looking for the largest number that EXCEL can handle (that actually is 9.99999999999999E+307 -- I have just used 9.9E+307), and when EXCEL can not find it,it reports back the last entry it had encountered in column A.
I hope this helps!
Yogi Anand, D.Eng, P.E.
ANAND Enterprises
Computers-Education-Engg-Networking
www.anandent.com yogia@hotmail.com
www.oakland.edu/~anand anand@oakland.edu
RE: Referencing latest entry - Excel
To start press ctrl+F3 to open the define name dialog.
Create a new name like Last_Entry, click OK.
Reopen the dialog and select the name from the list. At the bottom of the window is a box called "refers to". Delete whatever is there and insert this formula.
=OFFSET(Sheet1!$A$1,COUNTA(Sheet1!$A:$A)-1,0,1,1)
If your worksheet is not named sheet1 then change as required. If your data is not in column A then change the $A as needed. If you have empty cells above your data then change the -1 after the COUNTA statement, add 1 for every empty cell (1 empty cell requires the deletion of the -1, 2 empty cell requires a +1 in place of the -1 etc.).
Non empty cells after the last data entry will also effect the value of Last_Entry, I recommend keeping the column empty below your data.
In the cell which you want to display the most recent entry enter the formula =Last_Entry. This formula will work the same in any worksheet of the same workbook containing your data.
Good luck.