Displaying most recent entry on excell sheet via another sheet 3

[stevo30]

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

 

[ACtrafficengr]

[ol]
[li]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. [/li]
[li]Use Window/Arrange... to tile the two files.[/li]
[li]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.[/li]
[/ol]

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

 

[stevo30]

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

 

[cummings54]

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

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.

 

[ACtrafficengr]

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

 

[stevo30]

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

 

[ck1999]

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

 

[stevo30]

Thanks CK1999,
I shall give that ago also.

Regards
Stevo