How can I return the first non-blank cell in a range of formulas?

[HawaiianShirts]

I'm working with a spreadsheet that is used for data gathering. I've been asked to create an error checker for when data is pasted into the spreadsheet and overwrites the data validation rules.

In cells X3:AO33, I have a block of formulas. Most of them check for a blank cell, then check for valid entry in another cell. If the result is valid or not needed, it returns a blank. Otherwise, it returns a message. Here's an example:

=IF(OR(AND(D3<>"",OR(F3="",G3="",H3="")),AND(U3<>"",V3="")),CONCATENATE("Required fields are missing in row ",ROW(),"."),"")

Now I need to put something in B2 that looks for messages in the X3:AO33 range. I want it to return the value of the first non-blank cell. If Z5 has the first found error, then B2 should display the text in Z5. Then the user can fix the problem, which makes Z5 turn blank, and if the next error is in AB7, B2 should then display the text in AB7.

I've tried this.

=INDEX(X3:AO33,MATCH(FALSE,ISBLANK(X3:AO33),0))

And this.

=INDEX(X3:AO33,MATCH(TRUE,INDEX((X3:AO33<>0),0),0))

And this.

=INDEX(X3:AO33,MATCH(TRUE,LEN(X3:AO33)>0,0))

And this.

=MATCH("*",X3:AO33,0)

I've tried them all as arrays and not as arrays. And they all bring back the #N/A error.

Any ideas what I'm doing wrong?

 

[mikerickson]

Try = MATCH("Required*", X3:ZO33, 0)

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=ADDRESS(AGGREGATE(15,6,(ROW(X3:AO33)-ROW(X3)+1)/(X3:AO33<>""),1)+2,AGGREGATE(15,6,(COLUMN(X3:AO33)-COLUMN(X3)+1)/(X3:AO33<>""),1)+23)

 

[HawaiianShirts]

How about

=ADDRESS(AGGREGATE(15,6,(ROW(X3:AO33)-ROW(X3)+1)/(X3:AO33<>""),1)+2,AGGREGATE(15,6,(COLUMN(X3:AO33)-COLUMN(X3)+1)/(X3:AO33<>""),1)+23)

I updated my account to show Office 365. Thanks for the reminder.

This kind of worked. It returned the cell reference of the first cell with a message in it ($Y$3), so that's a lot closer than I was earlier today. How can I get the text instead?

 

[Fluff]

How about

="Required fields are missing in row " & AGGREGATE(15,6,(ROW(X3:AO33)-ROW(X3)+1)/(X3:AO33<>""),1)+2

 

[HawaiianShirts]

How about

="Required fields are missing in row " & AGGREGATE(15,6,(ROW(X3:AO33)-ROW(X3)+1)/(X3:AO33<>""),1)+2

Looks like I may not have been entirely clear in my explanation.

The data entry occurs in cells C3:V33. The error-checker is in X3:AO33. But not all the errors are about required fields being missing. Part of X3:AO33 looks more like this:

	Required fields are missing in row 3.
Start Date cannot be in the future in row 4.			Score must be 0-99 in row 4.
		ID cannot be blank in row 5.		Results must be Pass or Fail in row 5.
	Required fields are missing in row 6.

So what I'm trying to put in B2 should pick up "Required fields are missing in row 3" first, then when the user fills in those required fields, that message will disappear, and B2 then needs to show "Start Date cannot be in the future in row 4" because, going left-to-right and then top-to-bottom, that's the next message in this range.

 

[Fluff]

Maybe

=LET(Ary,INDEX(X3:AO33,AGGREGATE(15,6,(ROW(X3:AO33)-ROW(X3)+1)/(X3:AO33<>""),1),),INDEX(FILTER(Ary,Ary<>""),1))

 

[HawaiianShirts]

Maybe

=LET(Ary,INDEX(X3:AO33,AGGREGATE(15,6,(ROW(X3:AO33)-ROW(X3)+1)/(X3:AO33<>""),1),),INDEX(FILTER(Ary,Ary<>""),1))

No luck there either. I get a #Name error, so I guess my version of Excel (it says 365) must not have that function.

 

[Fluff]

You probably don't have the LET function yet, try

=INDEX(FILTER(INDEX(X3:AO33,AGGREGATE(15,6,(ROW(X3:AO33)-ROW(X3)+1)/(X3:AO33<>""),1),),INDEX(X3:AO33,AGGREGATE(15,6,(ROW(X3:AO33)-ROW(X3)+1)/(X3:AO33<>""),1),)<>""),1)