×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Referencing latest entry - Excel

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

RE: Referencing latest entry - Excel

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.

RE: Referencing latest entry - Excel

(OP)
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

RE: Referencing latest entry - Excel

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.

RE: Referencing latest entry - Excel

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)

RE: Referencing latest entry - Excel

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

RE: Referencing latest entry - Excel

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

RE: Referencing latest entry - Excel

(OP)
Appreciate all the help!

RE: Referencing latest entry - Excel

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 =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

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

RE: Referencing latest entry - Excel

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
www.anandent.com yogia@hotmail.com
www.oakland.edu/~anand anand@oakland.edu

RE: Referencing latest entry - Excel

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login



News


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close