Standard Excel Function or Needs VBA?

H

hellcat83

New Member
Joined
Aug 16, 2011
Messages
44
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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Sort by date Sort by votes
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?
 
Last edited:
Upvote 0
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

Code: 
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
 
Upvote 0
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
 
Upvote 0
That sounds hellishly complicated!

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

Rich (BB code): 
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
Maybe like this - it copies the value from column A to the same row in column B

Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
VBA Pivot Table Dynamic Range & Only One Selection per Row
Replies
3
Views
153
DanteAmor
DanteAmor
D
  • Solved
VBA to convert UTC date to Australian Eastern Standard Time (AEST)
Replies
2
Views
284
Deutz
D
M
  • Question Question
I need help with VBA/macro script to collect user input and fill cells
Replies
4
Views
461
mumps
mumps
T
  • Solved
=1+A2 in VBA
Replies
7
Views
101
StephenCrump
S
S
  • Solved
VBA linking Class(es) and Userforms
Replies
4
Views
475
Jaafar Tribak
Jaafar Tribak

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top