find first non blank cell in a row

M

mdmarsh

New Member
Joined
Sep 4, 2008
Messages
16
I'm back again. Hopefully, I won't be bothering you all much after this.

I need to know the value (which is a date) of the first non-blank cell in a range in a row. I have found some array formulas, but they either return it for the whole row (which I don't want), or they return text (which I don't want), or they don't work at all.

The range is: B9:FL9. Some of the cells in this range are blank, some are "0", and some contain dates. I need to know the most recent (furthest left) cell that contains a date and what that date is. Thanks!
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
If it really is the case that some cells are blank and others contain zeros and others contain dates, and you want to find the first (leftmost) date in the range B9:FL9, this would do that...remember to format the formula-containing cell as a date format so the serial number that gets returned by the formula is in a recognizable date format.

This is not an array formula, just enter it using the Enter key.

=INDEX(B9:FL9,MATCH(TRUE,INDEX((B9:FL9<>0),0),0))
 
Upvote 0
Thanks. That is much closer than anything else I have found. However, it only works on a row with no blank cells. If there is a blank cell ("") before the most recent date, it returns a blank cell. If there are only zeros and dates, it returns the most recent date. Did I do something wrong?
 
Upvote 0
It works for me with blank cells, so now let's define what "blank cell" means to you.

Is the cell you are saying is blank only "looking blank" because it does not display anything?

Or, really and truly, is the cell not empty, meaning maybe you have a null string formula in it, or a stray spacebar character, or anything (anything means anything) that is in the cell which you are claiming is blank.

Details please.
 
Upvote 0
The cell contains an IF function that returns "" if another cell is zero, so the cell contains "" b/c the cell it is referencing (on another worksheet=0)
 
Upvote 0
mdmarsh said:
The cell contains an IF function that returns "" if another cell is zero, so the cell contains "" b/c the cell it is referencing (on another worksheet=0)
Click to expand...

Try this
=INDEX(B9:FL9,MATCH(1,IF(B9:FL9<>0,IF(B9:FL9<>"",1)),0))

Ctrl+Shift+Enter
 
Upvote 0
Absolutely PERFECT!! Thank you so much. You've save me hours and hours. I'm new to this, but it has been fun figuring out all excel can do! Thanks for your help.
 
Upvote 0
mdmarsh said:
The cell contains an IF function that returns "" if another cell is zero, so the cell contains "" b/c the cell it is referencing (on another worksheet=0)
Click to expand...
OK, so you have null string formulas in the range, meaning non-empty cells...

Edit, I see snasui just posted a formula, which is an array (Ctrl+Shift+Enter) that works for me, so it looks like you should be good to go.
 
Upvote 0
What If I have a Date Range that goes across the Top with the Lower being on the Left and the Higher being on the Right and I want to select the most recent one. IS there any way to do this without flipping the date range
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Solved
Refer to last non-empty cell in a row
Replies
4
Views
168
MARK858
MARK858
K
  • Solved
Use 'Unique', 'Filter' and 'If' statements.
Replies
7
Views
251
Fluff
Fluff
R
  • Question Question
Linked dynamic named ranges - unexpected results.
Replies
2
Views
199
Ramses505
R
T
  • Solved
Large Sheet-Need to Get blank cells to equal proceeding cell when blank
Replies
2
Views
55
TWTHOMAS
T
L
  • Solved
Return non-blank cells in an array to a single column
Replies
3
Views
584
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,620
Messages
6,179,928
Members
452,949
Latest member
beartooth91

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