Formula to indicate if row in Col B is empty with data in same row Col A

**c.bernardo** · 04-22-2017, 10:24 AM

Hello,

I am trying to flag potential user input errors/omissions on a sheet. The user enters information in Col A and then subsequently in Col B. I would like to notify the user if there is information in Col A but not in Col B.

So far I've tried:

Please Login or Register  to view this content.

I've also tried

Please Login or Register  to view this content.

but neither of these are what I'm looking for.

I would like to have a formula that looks at the entire array A1:B21 to evaluate the criteria, instead of row by row. I've attached an example sheet.

Thank you very much in advance!

Chris

**Glenn Kennedy** · 04-22-2017, 10:42 AM

If there is ALWAYS only one row (or no rows) with data missing - this array formula will do this. Is it possible for there to be more than one missing value?

=IFERROR("Data missing for "&INDEX($A$2:$A$10,MATCH(1,($A$2:$A$10<>"")*($B$2:$B$10=""),0)),"No data missing")

Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

**Glenn Kennedy** · 04-22-2017, 10:47 AM

If, however, there can be more than one row with data missing, use this array formula, copied down as far as needed:

=IFERROR("Data missing for "&INDEX(A:A,SMALL(IF($A$2:$A$10<>"",IF($B$2:$B$10="",ROW($B$2:$B$10))),ROWS($C$2:C2))),"")

**MarvinP** · 04-22-2017, 10:48 AM

Hi c.bernardo,

This seems like an appropriate place to use VBA. Here is the Event Code behind the sheet.

Please Login or Register  to view this content.

And the attached file below so you can test it. Type something in the first blank row in column A and it will check and give a message...
MsgBox to fill in blank B on Event Change.xlsm

**dkhanknu** · 04-22-2017, 10:56 AM

Has been deleted.

**c.bernardo** · 04-22-2017, 11:09 AM

Glenn this worked great! I have a follow-up question. Will this formula work in a hidden sheet, from which a visible "Flags" sheet pulls? My intent is to hide the sheet with the formula you provided, but display a protected sheet called "Flags" that displays the "Flags" revealed by your formula, which will reside on the hidden sheet.

Thanks again for the help

**Glenn Kennedy** · 04-22-2017, 11:14 AM

Yep. No problem. just make sure that the formula is pointing to the right sheet...

**AlKey** · 04-22-2017, 11:24 AM

Or maybe this

Formula:

Please Login or Register  to view this content.

v	A	B	C
1	Fruit	Color
2	Apple		Data B missing for A
3	Pear	Green

**dangelor** · 04-22-2017, 11:31 AM

Possibly...

Formula:

Please Login or Register  to view this content.

**c.bernardo** · 04-22-2017, 02:50 PM

Thank you to everyone who contributed to this thread. I tested all your solutions and they all worked to some extent. Glenn's gives me exactly what I was looking for and is the best fit for my "Flag" sheet use.

Thank you all, I appreciate your time and effort

Chris

**JohnTopley** · 04-22-2017, 02:52 PM

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Formula to indicate if row in Col B is empty with data in same row Col A

LinkBack

Thread Tools

Rate This Thread

Display

Formula to indicate if row in Col B is empty with data in same row Col A

Re: Formula to indicate if row in Col B is empty with data in same row Col A

Re: Formula to indicate if row in Col B is empty with data in same row Col A

Re: Formula to indicate if row in Col B is empty with data in same row Col A

Re: Formula to indicate if row in Col B is empty with data in same row Col A

Re: Formula to indicate if row in Col B is empty with data in same row Col A

Re: Formula to indicate if row in Col B is empty with data in same row Col A

Re: Formula to indicate if row in Col B is empty with data in same row Col A

Re: Formula to indicate if row in Col B is empty with data in same row Col A

Re: Formula to indicate if row in Col B is empty with data in same row Col A

Re: Formula to indicate if row in Col B is empty with data in same row Col A

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] How execute formula using data from A1 to A... (until empty cell in B)

[SOLVED] Need formula to move data on same row and deleting empty rows between data

Formula to read data from different sheet, eliminating empty cells

Data validation formula to show values of not empty cells

Trimming empty cells that are not considered empty or blank because they have a formula??

Export xml - avoid exporting empty formula vaues as empty xml tags

SOLVED: Cell that formula is mining data from is empty, want no result

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions