IF Formula depending on multiple columns in an array and and multiple rows

clevey

New Member
Joined
Jan 22, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Hello! I'm having an issue that seems possibly beyond Excel's capabilities, but thought I'd check a few forums before giving up.

I'm creating a template to distribute to multiple people that asks them to enter a list of items and fill out several fields for each item. I'm trying to create a "flag" so that if they leave a column blank, an error message will pop up above the column. The problem I'm having is that these lists can be various lengths, and any of the rows could possibly have this error, but I need the warning to be triggered only by the rows being used for that person's template. I want the warning message to appear in one cell at the top of the sheet and would love to avoid having a separate error check beside every single row.

So to make an example, I've got a template that allows up to 5 entries, but they might not use all 5. In this case, the person only needs 3 of the 5 rows. I need to put a warning message in cell E1 if they leave an empty space somewhere in column E, but only for the rows with values in column B. Of course cell E6 in this case and any cells below it will be blank since there's only 3 items on the list, but some people might use all 5 rows, in which case I need to check all 5. Here, I'm only worried about the blank in cell E4. Is there a way to write an IF formula that reads all of the rows in the table, but is only triggered if column B of that row is non-blank and column E of that same row is blank? I feel like this will involve an array but I'm not sure.
ABCDEF
1[ERROR FLAG]
2TRANSACTION IDCOMPANYCOUNTRYCATEGORY
31ABC1234Company 1USACommission
42DEF1234Company 2UK
53GHI1234Company 3USAFee
64
75

Any suggestions would be super appreciated! Thanks!

-Claudia
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,693
Office Version
  1. 365
Platform
  1. Windows
Something like this? I've added two methods, the one in column E just tells you that there is an error, the one in column C tells you which line the error is on.
If there is more than one error it will show the first one, once that is corrected the formula will update to show the next error.
Book1(AutoRecovered)2 (version 1).xlsb
ABCDE
1Error found in line 2Error
2TRANSACTION IDCOMPANYCOUNTRYCATEGORY
31ABC1234Company 1USACommission
42DEF1234Company 2UK
53GHI1234Company 3USAFee
64
75
Sheet1
Cell Formulas
RangeFormula
C1C1=IFERROR("Error found in line "&AGGREGATE(15,6,A3:A7/(B3:B7<>"")/(E3:E7=""),1),"Looks Good")
E1E1=IF(SUMPRODUCT((B3:B7<>"")*(E3:E7="")),"Error","Looks Good")
 
Solution

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,831
Welcome to the MrExcel forum!

Here's a formula that works for your current example:

Book1
ABCDE
1Error
2TRANSACTION IDCOMPANYCOUNTRYCATEGORY
31ABC1234Company 1USACommission
42DEF1234Company 2UK
53GHI1234Company 3USAFee
64
75
Sheet4
Cell Formulas
RangeFormula
E1E1=IF(COUNTIF(E3:E7,"<>")<COUNTIF(B3:B7,"<>"),"Error","")


Whether or not it works for all conditions in this example depends on what conditions you want. And if you want it to work for multiple templates, we'd need to see those templates too.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,693
Office Version
  1. 365
Platform
  1. Windows
Whether or not it works for all conditions in this example depends on what conditions you want.
I thought about a formula similar to yours, Eric. I decided against it on the chance that a different row might be empty in column B but completed in column E which would incorrectly cancel out the empty cell in column E (based on the conditions laid out so far).

Although, as you rightly point out, there may be other conditions that it will not identify correctly.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,831

ADVERTISEMENT

I thought about a formula similar to yours, Eric. I decided against it on the chance that a different row might be empty in column B but completed in column E which would incorrectly cancel out the empty cell in column E (based on the conditions laid out so far).

Although, as you rightly point out, there may be other conditions that it will not identify correctly.
Exactly. I started with a very basic formula until we know whether we need to be concerned about multiple rows, multiple columns, rows with data in different columns, columns that must be text, columns that must be numeric, columns that must have data validation on them, etc. And if there are different templates, is there a way to come up with an omnibus formula? There are too many unknowns still, I'd hate to come up with a great formula that just doesn't apply. I liked your idea of showing the bad row though. In a larger sheet, that could be very useful.
 

clevey

New Member
Joined
Jan 22, 2021
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
Exactly. I started with a very basic formula until we know whether we need to be concerned about multiple rows, multiple columns, rows with data in different columns, columns that must be text, columns that must be numeric, columns that must have data validation on them, etc. And if there are different templates, is there a way to come up with an omnibus formula? There are too many unknowns still, I'd hate to come up with a great formula that just doesn't apply. I liked your idea of showing the bad row though. In a larger sheet, that could be very useful.
@jasonb75 and @Eric W
OMG you guys are AMAZING!!!! The real template has 15k available rows and ~20 fields that I wanted to flag for ~20 different reasons (blank cells, negative numbers, text inputs that are too long or too short, etc.), with some fields needing line references and some just general warnings, and I ended up using a combo of both of your answers. Thank you so much!

PS. I'm so impressed with how quickly you both responded-- I was asked to get this done by the end of the workday and nearly quit on the spot :ROFLMAO: You're absolute heroes.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,693
Office Version
  1. 365
Platform
  1. Windows
I liked your idea of showing the bad row though. In a larger sheet, that could be very useful.
Thanks, Eric.
I use something similar to identify anomalies in a sheet that I use, I prefer to do it with textjoin to show all errors as a delimited list so had to compromise a little for the OP's excel version.

@clevey we're always glad to help, next time you need an answer to the impossible, you know where to look :)
 

Watch MrExcel Video

Forum statistics

Threads
1,130,309
Messages
5,641,447
Members
417,209
Latest member
Agbarker

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
Top