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
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
- 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.
- Use Window/Arrange... to tile the two files.
- 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
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
CODE
Sheet2.Cells(1, 1) = Target.Value
End Sub
RE: Displaying most recent entry on excell sheet via another sheet
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
RE: Displaying most recent entry on excell sheet via another sheet
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
I shall give that ago also.
Regards
Stevo