×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

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!
  • Students Click Here

*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

Jobs

Displaying most recent entry on excell sheet via another sheet
3

Displaying most recent entry on excell sheet via another sheet

Displaying most recent entry on excell sheet via another sheet

(OP)
Hi,
My problem is as follows we currently enter data on a regular basis into an excel spreadsheet, but i need to display the latest entry on a different report. Is there any formula that i can use which would automatically update the final report if any new entries are entered on the first spreadsheet?

Regards

Stevo

RE: Displaying most recent entry on excell sheet via another sheet

  1. Open one of the spreadsheets. Using File/Open, open the other spreadsheet. This way, both sheets are open in the same instance of Excel, rather than separate instances.
  2. Use Window/Arrange... to tile the two files.
  3. Go to the cell in your desination spreadsheet where you want the data to show. Type '=', then point to the cell in the source spreadsheet with the data you want.
When you open the second spreadsheet, it will ask you if you want to update the data from the other sheet.

For more info, read "About linking to another workbook or program" in Excel Help.

     "...students of traffic are beginning to realize the false economy of mechanically controlled traffic, and hand work by trained officers will again prevail." - Wm. Phelps Eno, ca. 1928

"I'm searching for the questions, so my answers will make sense." - Stephen Brust

RE: Displaying most recent entry on excell sheet via another sheet

(OP)
Hi ACtrafficengr,
Thanks for your answer, i was aware of this method but i think i need to explain further. Everytime the first sheet is updated, it is in a new cell so that all previous entries are still there. So i was hoping for some sort of "latest date closest to todays date" formula. Is there such a thing?

regards
Stevo30

RE: Displaying most recent entry on excell sheet via another sheet

You need to do this sort of thing using VBA programing.  A worksheet has several associated events which can be used to trigger a procedure.  For example the simple event procedure

CODE

Private Sub Worksheet_Change(ByVal Target As Range)
  Sheet2.Cells(1, 1) = Target.Value
End Sub
will cause any cell that changes in the worksheet to be written to cell A1 in sheet2.  Of course you can make this more usefull by writing the target cell to a location in your "latest" report based on the address of the cell that changes.
 

RE: Displaying most recent entry on excell sheet via another sheet

If the latest entry is the bottom row in a column, you can use a VLOOKUP function in the other spreadsheet.

It will look something like this:

Workbook 1:
     |     1         |  2
A  | 10/7/2006   20
B  |  5/2/2007    18
C  | 10/2/2007   25

Workbook 2:

   =VLOOKUP(TODAY(),[Book1]Sheet1!B1:C3,2)

Result = 25

     "...students of traffic are beginning to realize the false economy of mechanically controlled traffic, and hand work by trained officers will again prevail." - Wm. Phelps Eno, ca. 1928

"I'm searching for the questions, so my answers will make sense." - Stephen Brust

RE: Displaying most recent entry on excell sheet via another sheet

(OP)
Thanks guys (ACtrafficengr, cummings54) i think from this i should be able to sort it

RE: Displaying most recent entry on excell sheet via another sheet

You could use this code in the report workbook

Sub getinfo()

Dim wb As Excel.Workbook
Dim sh As Excel.Worksheet

   wbname = ActiveWorkbook.Name
   Set wb = Workbooks.Open("\\server\network\data.xlsx")
    Set sh = Worksheets("sheet1")
    valuewanted = Range("B" & sh.Range("b1").End(xlDown).Row).Value
    wb.Close False
Set wb = Nothing
Set sh = Nothing
Workbooks(wbname).Activate
Sheets("sheet1").Range("a2").Value = valuewanted

End Sub

This opens your data spreadsheets and finds the last entry in column B and then copies that data into your current workbook (in my case in  cell a2)

ck1999

RE: Displaying most recent entry on excell sheet via another sheet

(OP)
Thanks CK1999,
I shall give that ago also.

Regards
Stevo

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!


Resources