retrieving data from adjacent columns
Posted by greylock on October 11, 2000 10:39 AM
lets say i have 2 columns A & B. i want to write a
formula in B1 that does the following. it retrieves
the latest cell entry in column A, regardless of
location for example one day it could be A5 another
A56. how can i accomplish this. thanks greylock
Posted by David on October 12, 2000 2:04 AM
Try putting this in the worksheet.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not IsError(Target) Then
If Target.Column = 1 And Target.Count = 1 Then
If Target <> "" Then
Worksheets(1).Range("B1") = Target
End If
End If
End If
End Sub
Posted by Celia on October 12, 2000 2:32 AM
Or alternatively, to cover also the possibility of a range of cells being selected(including one or more cells in column A) and the entry being made by Ctrl+Enter :-
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Range("A:A"), Target) Is Nothing Then
Range("B1") = Intersect(Range("A:A"), Target).Cells(1, 1)
End If
End Sub
Posted by Ivan Moala on October 12, 2000 2:33 AM
OR you could try this formula in B1
=INDIRECT("A" & COUNTA((A:A)))
The only problem is that the data in column 1
must be continuous ie no blanks
Ivan
Posted by DAvid on October 12, 2000 12:00 PM
Very nice and short celia but Target.Count = 1 takes care of a range being selected
Posted by Celia on October 12, 2000 4:11 PM
David
Yes, but that means your code only runs if the user enters in one cell only in column A.
My code will run when only one cell is entered and also when a range of cells or multiple cells that intersect column A are entered with Ctrl+Enter.
Celia
Posted by greylock on October 12, 2000 4:55 PM
Re: this is what i really want
I want to enter in cell B2 the first cell in
column A with a value. there are plenty of blank
cells in column A and the cells containing values
move from day to day. For example, one day the
first cell with a value will be A9, the next day
it could be A39...how can i do this?
Posted by Tim Francis-Wright on October 13, 2000 1:09 PM
Re: this is what i really want
If what you want is the first value from
column A, use
=OFFSET($A$1,MIN(IF(ISNUMBER($A$1:$A$10000),ROW($A$1:$A$10000)))-1,0)
[this is a control-shift-enter formula]
If you want the cell reference, use
="A"&MIN(IF(ISNUMBER($A$1:$A$10000),ROW($A$1:$A$10000)))
[also a control-shift-enter formula
I've assumed that you're looking for the
first cell with a number in it--you might
need a different IS function.
HTH
