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

 

[squidgeny]

Will the most recent entry always be the last entry in the range?

What I mean is, how will the range fill up? Will it start with A1, then A2, etc?

 

[VoG]

If you mean the bottom filled value then

=LOOKUP(9.9E+307,A1:A10)

If you really mean the most recently filled value, right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then Range("B1").Value = Target.Value
End Sub

 

[hellcat83]

Hi VoG

That worked great. The only problem is that in reality the scenario is a bit more complex. Firstly, the range lives in another sheet within the workbook. I assume the range definition will just include the sheet name too.

The more complex part is that the cell that displays the most recent entry won't always do that.

The purpose of the sheet is to display student data for teachers to use, that changes dynamically based on the time of year (we use different data at different times). Most of the time the columns need only call previously defined values in other sheets, but some of the time need to display this most recent entry data.

Would I be able to nest the code you posted inside of a series of ifs using dates as the criteria for various actions?

Does that makes sense? I an completely new to VBA so don't really know what it can do.

Cheers
Jim

 

[VoG]

That sounds hellishly complicated!

How about a simpler approach. Change Data to the name of the sheet to copy the value to

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
    If MsgBox("Do you want to copy this value", vbYesNo + vbQuestion) = vbYes Then Sheets("Data").Range("B1").Value = Target.Value
End If
End Sub

 

[hellcat83]

So is there no way of even placing the contents of a cell in one sheet into another?

 

[VoG]

Yes. Put that code into the module of the sheet that you want to copy FROM.

Change Data to the name of the sheet you want to copy TO.

 

[hellcat83]

Ah ok, now I see what you mean.

Thanks for your help.

Jim

 

[hellcat83]

If I want to use the first piece of code, how would I code it to have a for loop that conducts the same thing with the same columns but for a range of rows, with the entries in one row not affecting the other.

In other words, each pupil has a row of the sheet and I want a column that shows each pupil's latest result. So I can use the code you gave me to start with, but it needs a for loop round it I would imagine. The problem is that changing the row numbers with a loop variable name is creating an error.

Any ideas?

 

[VoG]

Maybe like this - it copies the value from column A to the same row in column B

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A10")) Is Nothing Then Range("B" & Target.Row).Value = Target.Value
End Sub