Is there a function for this...?


  • Is there a function that I can use that allows a cell to display the latest cell entry from other sheets? For example:

    I have 5 sheets in a workbook. The user can input txt in cell A1 on sheets 1-4. Is there a function that can display the the latest txt entry in cell A1 from sheets 1-4? The latest txt entry wont necessarily be sheet 4.

    mercoledì 14 marzo 2012 15:35


  • No, there is no function, but you can use event code. Copy the code below, press Alt-F11, Press Ctrl-R to open the project exmplorer, find your workbook in the window that appears and double click it. Then look for the ThisWorkbook object (you may need to expand the "Microsoft Excel Objects" folder if you are in the folder view). Double click the ThisWorkbook object, and paste the code into the window that appears. Change the Sheet5 to the tab name of the sheet where you want the entered values to be stored.

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Address = "$A$1" Then
    Application.EnableEvents = False
    Worksheets("Sheet5").Range("A1").Value = Target.Value
    Application.EnableEvents = True
    End If
    End Sub

    HTH, Bernie

    mercoledì 14 marzo 2012 16:22