Find first non-blank cell and return associated date in related column

ryank_2002

New Member
Joined
Apr 12, 2012
Messages
4
I am looking for a formula in excel that will first search for the first non-blank cell in a column and return a date in a different column that is associated with that first non-blank cell. I've seen a few formulas around that are close, but nothing has worked for me so far.

For example:
-Column A lists all of the dates in sequential order
-Column B lists recorded values over certain days, but not every day has a value recorded.
-Let's say that cell B5 is the first filled in cell in column B, I'm looking for a formula to return the date in cell A5. So looking at the example below when it comes to B5 which is the first filled in cell, B5=100, it would return A5, A5=2/5/2012.

......A ...........B
1 2/1/2012
2 2/2/2012
3 2/3/2012
4 2/4/2012
5 2/5/2012 ...100

(the periods are only to make the fake rows/columns kind of line up)

I'd also like a formula that returns the date associated with the last filled in cell in a column.

Any ideas? Any help would be appreciated!
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
hi ryank_2002
i think this is what you need


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("b" & Rows.Count).End(xlUp).Offset(0, -1).Select
Range("f1") = Selection ,you may need to change this cell to whatever cell you want the date to go to and format the cell as date
End Sub

cheers
kevin
 
Upvote 0
I was hoping to use just an excel formula, not a macro. I have to send the file to people who don't know how to use macros and it will make things too complicated. I've tried the index(match...) combinations with no success, but I was wondering if there was some way vlookup could work, but I'm not sure how to use that to look for a non-blank cell.

Any other ideas?
 
Upvote 0
I am looking for a formula in excel that will first search for the first non-blank cell in a column and return a date in a different column that is associated with that first non-blank cell. I've seen a few formulas around that are close, but nothing has worked for me so far.

For example:
-Column A lists all of the dates in sequential order
-Column B lists recorded values over certain days, but not every day has a value recorded.
-Let's say that cell B5 is the first filled in cell in column B, I'm looking for a formula to return the date in cell A5. So looking at the example below when it comes to B5 which is the first filled in cell, B5=100, it would return A5, A5=2/5/2012.

......A ...........B
1 2/1/2012
2 2/2/2012
3 2/3/2012
4 2/4/2012
5 2/5/2012 ...100

(the periods are only to make the fake rows/columns kind of line up)

I'd also like a formula that returns the date associated with the last filled in cell in a column.

Any ideas? Any help would be appreciated!
Try this...

Array entered**:

=INDEX(A2:A10,MATCH(TRUE,B2:B10<>"",0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Format as Date
 
Upvote 0
Thank you! That works.

Do you have an equation to find the last non-blank cell in the range?
Try these...

Book1
ABCDE
22/1/2012__Last numberLast Date
32/2/2012__672/16/2012
42/3/2012____
52/4/2012____
62/5/2012100___
72/6/2012____
82/7/201252___
92/8/201288___
102/9/2012____
112/10/2012____
122/11/2012____
132/12/2012____
142/13/2012____
152/14/201233___
162/15/2012____
172/16/201267___
182/17/2012____
192/18/2012____
202/19/2012____
Sheet1

For the last (bottom-most) numeric value from column B:

=LOOKUP(1E100,B2:B20)

For the date that corresponds to the last (bottom-most) numeric value in column B:

=LOOKUP(1E100,1/B2:B20,A2:A20)

Format as Date
 
Upvote 0
Thank you, that works as well!

For my infromation, what function does the "1E100" serve in the formula? Would there also be a way to use that formula to look up the first non-blank cell?
 
Upvote 0
Thank you, that works as well!

For my infromation, what function does the "1E100" serve in the formula?
1E100 is scientific notation which is a "shorthand" method of expressing very long numbers. 1E100 is the very large number 1 followed by 100 zeros. For the formula to work as expected the lookup_value (1E100) has to be greater than any number in the range. Since 1E100 is such a huge number there is a 100% chance that it is greater than any number in the range so the formula works as expected.

Would there also be a way to use that formula to look up the first non-blank cell
The formula in post #4 does that.
 
Upvote 0
This formula has helped me, but I have a more complex list I am trying to create. I have a list "Master Log" on sheet one where I have a running list that will continually be updated. That list goes vertically, horizontally is a list of the other sheets in the workbook that a change in the list would apply to. What I am trying to create is that when I add something to the list and put an "X" under the column of the affected worksheet, that when I go to that affected worksheet, the information that was added to the "Master Log" is populated on a list for that worksheet. The goal would be to skip any information that doesn't have an "X" for that specific worksheet, and not duplicate any entry's on the "Master Log". This is roughly what my "Master Log" looks like. This would be a running list that would be added to over time. I would like Sheet 1 in my workbook to generate a list with only the entries on 5/1/14, 5/3/14, and 5/8/14 in the example below. Any help is appreciated.
Date
Change Made
Sheet 1
Sheet 2
Sheet 3
5/1/14
Test 1
X
5/2/14
Test 2
X
5/2/14
Test 3
X
X
5/3/14
Test 4
X
5/5/14
Test 5
X
5/8/14
Test 6
X
5/8/14
Test 7
X
X
X

<tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,293
Members
448,564
Latest member
ED38

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