Standard Excel Function or Needs VBA?

[hellcat83]

Hi

I have a scenario that I have no idea how to solve using standard functions.

I would like to take a range of cells with will over time be filled with data, and then in another cell display the most recent entry in the initial range.

For example, A1:A10 are initially empty, but over time will be filled with numbers. I would like to find a formula to go in cell B1, that allows me to display the most recently entered value in the A1:A10 range.

I've seen some people suggest that this can't be done using standard functions, but I thought I'd try for ideas to the contrary before I dive into learning VBA to do it.

Cheers
Jim

 

[VoG]

To post your code

[code]
paste your code here
[/code]

 

[hellcat83]

Private Sub Worksheet_Change(ByVal Target As Range)
''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''
' This section appends any new exam mark entry with an "e" for later formatting.
Dim DivRg As Range
'''''''''''
Set DivRg = Range("BJ11:BL320")
Set DivRg = Application.Intersect(Target, DivRg)

If DivRg Is Nothing Or Target.Cells.Count > 1 Then Exit Sub

Application.EnableEvents = False
Target = Target & "e"
Application.EnableEvents = True

Set DivRg = Nothing
'''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''
Dim Row As Integer
''''''''''''''''''''''''''''''''''''''''
' This section monitors assessment columns, placing the most recent entry for each pupil in
' additional columns in management sheet.
''''''''''''''''''''''''''''''''''''''''
' Biology Assessments Y7
MsgBox Target
For Row = 11 To 303
    FirstCol = "X" & Row
    SecCol = "AA" & Row
    ThirdCol = "AJ" & Row
    FourthCol = "AL" & Row
    FifthCol = "BJ" & Row
    OutCol = "FA" & Row
    If Not Intersect(Target, Range(FirstCol & ":" & SecCol & "," & ThirdCol & ":" & FourthCol & "," & FifthCol)) Is Nothing Then Sheets("KS3 Data Management Sheet").Range(OutCol).Value = Target.Value
Next Row
End Sub

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
''''''''''''''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''
' This section appends any new exam mark entry with an "e" for later formatting.
Dim DivRg As Range
'''''''''''
If Target.Count > 1 Then Exit Sub
Set DivRg = Range("BJ11:BL320")
Set DivRg = Application.Intersect(Target, DivRg)

If Not DivRg Is Nothing Then

    Application.EnableEvents = False
    Target = Target & "e"
    Application.EnableEvents = True
    
    Set DivRg = Nothing
End If
'''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''
Dim Row As Integer
''''''''''''''''''''''''''''''''''''''''
' This section monitors assessment columns, placing the most recent entry for each pupil in
' additional columns in management sheet.
''''''''''''''''''''''''''''''''''''''''
' Biology Assessments Y7
MsgBox Target
For Row = 11 To 303
    FirstCol = "X" & Row
    SecCol = "AA" & Row
    ThirdCol = "AJ" & Row
    FourthCol = "AL" & Row
    FifthCol = "BJ" & Row
    OutCol = "FA" & Row
    If Not Intersect(Target, Range(FirstCol & ":" & SecCol & "," & ThirdCol & ":" & FourthCol & "," & FifthCol)) Is Nothing Then Sheets("KS3 Data Management Sheet").Range(OutCol).Value = Target.Value
Next Row
End Sub

 

[hellcat83]

Thanks, works perfectly.
Jim

 

[hellcat83]

Hi back again.

I've been using this code very nicely for a few weeks now, but I have encountered an issue with colleagues making alterations to sheets they shouldn't be going in. As a result, my boss has asked me to split the file into two halves, each for different people, but as a result this code now spans two files.

I've searched the net, but can't find the right way to structure the statement. I'm guessing it should be something in this line;

    If Not Intersect(Target, Range(FirstCol & ":" & SecCol & "," & ThirdCol & ":" & FourthCol & "," & FifthCol)) Is Nothing Then Sheets("C:\Users\jha\Documents\Teaching Stuff\Data Managment & Tracking Sheets\KS3\Modified\KS3_2010_data_output\KS3 Data Management Sheet").Range(OutCol).Value = Target.Value

I've added the file path into the Sheet call up, but it doesn't like it.

Any ideas???
Jim