Comments based on content of range

ChristineJ

Well-known Member
Joined
May 18, 2009
Messages
756
Office Version
  1. 365
Platform
  1. Windows
I'd like to see if there are ways to return three different comments based on violations of "my rules" for amounts in range B1:C11.
Rule 1 - all numbers in column B must be entered before any numbers in column C are entered
Rule 2 - no rows should be blank if a number appears in either column in the following row
Rule 3 - only one number should appear in each row

If a rule is violated, a comment would appear in column E. Each rule's comment would appear in a different row. Thanks!

1613398742932.png
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Hello ChristineJ - For Issue 2 -> you may try something like the formula below. Try it in a practice worksheet in cell E1 and fill down. The "X" is just there to show that something is being done. You can easily make it "" and it won't show up. If you are familiar with VBA all 3 Issues could probably be coded in VBA in a macro. Hope this helps you get started.

=IF(AND(A1="", B1="",OR(OFFSET(A1,1,0,1,1)<>"",OFFSET(B1,1,0,1,1)<>"")),"Issue 2 -> No rows should be left blank if there is an amount on the next row.","X")

Alternate formula with "X" replaced by "".
=IF(AND(A1="", B1="",OR(OFFSET(A1,1,0,1,1)<>"",OFFSET(B1,1,0,1,1)<>"")),"Issue 2 -> No rows should be left blank if there is an amount on the next row.","")
 
Upvote 0
Thanks, goesr! Yes, this is a great start. I can go from here. Appreciate the help!
 
Upvote 0
Hi,

I'm a bit confused with your Rule # 1 and Rule # 3,
if Rule 1 - All numbers Must be entered in Column B before Any numbers can be entered in Column C
and Rule 3 - Only one number should be in each row.
These two rules are in conflict? Or, maybe I'm just not understanding...

Anyway, this seems to do what your sample shows, but I still think there's a conflict of logic between Rule 1 & 3...

Book3.xlsx
BCD
11 
22 
33 
44Issue 1
55 
6Issue 2
766Issue 3
87Issue 1
98Issue 1
10 
11 
Sheet768
Cell Formulas
RangeFormula
D1:D11D1=IF(COUNT(B1:C1)=2,"Issue 3",IF(COUNT(B$1:B$11)<>11,IF(COUNT(C1),"Issue 1",IF(AND(COUNT(B1:C1)=0,COUNT(B2:C2)),"Issue 2",""))))
 
Upvote 0
try this in A1 copied down in A2: A11

Excel Formula:
=IF(COUNTA(B1:C1)=2,"Error3",IF(AND(COUNTA(B1:C1)=0,COUNTA(B2:C2)>0),"Error2",IF(COUNTA(C1)=1,IF(ROW(C1)<IFERROR(LOOKUP(2,1/($B$1:$B$11<>""),ROW($B$1:$B$11)),0),"Error1",""),"")))

the 3 components
=IF(COUNTA(C1)=1,IF(ROW(C1)<IFERROR(LOOKUP(2,1/($B$1:$B$11<>""),ROW($B$1:$B$11)),0),"Error1","OK"),"OK")
=IF(AND(COUNTA(B1:C1)=0,COUNTA(B2:C2)>0),"Error2","OK")
=IF(COUNTA(B1:C1)=2,"Error3","OK")
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
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