Referencing latest entry - Excel 1

[m1280m]

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

 

[dbaird]

Just set the "reference cell" equal to the cell the last data is entered into.

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.

 

[m1280m]

Thanks for the reply, but I'm adding new data in a new cell each day. I'm needing my "reference cell" to display the last entry for that column. For instance if I have data in cells A1 thru A30 I want my "reference cell" to show the data in cell A30, but the next day there will be a new value placed in A31, which I now want to display in my "reference cell". I was curious if there is a function, similiar to the "max" or "min" function, that will do this for me.

Thanks again

 

[jhardy1]

m1280m,

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.

 

[MintJulep]

One possible method:

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)

 

[jhardy1]

MintJulep,

"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.)

 

[MintJulep]

Oops, Julian is right. Vlookup. The names never made sense to me.

 

[m1280m]

Appreciate all the help!

 

[joerd]

You can also use one of the following:
1. if your data has no "gaps", i.e. blank lines, then you can reference the last non-blank cell by [tt]=OFFSET(A1,COUNTA(A:A)-1,0)[/tt]
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):[tt]=INDIRECT(ADDRESS(MAX((A1:A100<>"")*ROW(A1:A100)),COLUMN(A1:A100)),4)[/tt]

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.

 

[yakpol]

Another formula very similar to joerd's #1 solution
=INDEX(A1:A100,COUNTA(A1:A100))

 

[yogia]

Hi m1280m:

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

http://www.anandent.com

yogia@hotmail.com

http://www.oakland.edu/~anand

anand@oakland.edu

 

[bpeirson]

Try using a dynamic range.

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.