# IF statement Conundrum

#### kudakaswa14

##### New Member
Hi All,

I have two columns. Column A has numbers in some rows while Column B has a drop down which allows someone to classify the numbers in Column B e.g. asset, expense, revenue etc.

I would like a formula which will return "Please complete SCOA classification in full" where there is a number in column A but individual hasn't classified in column B and where all cells that have numbers in A have been classified to return "SCOA classification complete".

Its basically a check which ensures that the spreadsheet is complete. This will come on the dashboard.

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Try this in C1:

=IF(A1<>"",IF(B1<>"","","Please complete SCOA classification in full"),"")

One of these to check all complete (they will need adjusting to suit):

=IF(SUMPRODUCT(--(A1:A10<>""),--(B1:B10<>""))<>COUNT(A1:A10),"ERROR","SCOA classification complete")
=IF(COUNTIF(C1:C10,"Please complete SCOA classification in full")=0,"SCOA classification complete","ERROR")

Heres some vba code that can be added really easily. Alt +f11 double click your sheet on the left hand side and paste the code in. Exit window. Then alt +F8 . Youll see one item in the list if only marco in sheet. Choose run and it do the check for you.

Code:
``````Sub Test ()

LastRowColA = range( "a" & rows.count).end(xlup).row

For Each Cell in Range("A2", Range("A" & LasRowColA)) 'Sets Loops for all items in list based off column a

If Cell.Offset(1,0).Value = "" Then  ' Checks column b is empty
Cell.Offset(2,0) = "Please complete SCOA classification in full"
End If

If Not Cell.Offset(1,0).Value = "" Then
Cell.Offset(2,0).Value = "SCOA classification complete"
End If

Next Cell

End Sub``````

Try this in C1:

=IF(A1<>"",IF(B1<>"","","Please complete SCOA classification in full"),"")

One of these to check all complete (they will need adjusting to suit):

=IF(SUMPRODUCT(--(A1:A10<>""),--(B1:B10<>""))<>COUNT(A1:A10),"ERROR","SCOA classification complete")
=IF(COUNTIF(C1:C10,"Please complete SCOA classification in full")=0,"SCOA classification complete", ERROR")

Hi Steve my issue with the first formulae is that it returns that for all incomplete cells in A which have no classification in B.I want it to return that message for only cells in A with numbers in them (exclude blank cells) that haven't been classified in B. reason is the spreadsheet is for data to be entered in 500 rows but some people may only enter 350 rows worth of data hence the un entered rows would return that message as they are blank, which isn't ideal. so its basically up to when you have entered your numbers return "please complete" if you have entered numbers for which you haven't classified.

So you need it altered to ignore text in "A"??

=IF(A3<>"",IF(NOT(ISNUMBER(A3)),"",IF(B3<>"","","Please complete SCOA classification in full")),"")

So you need it altered to ignore text in "A"??

=IF(A3<>"",IF(NOT(ISNUMBER(A3)),"",IF(B3<>"","","Please complete SCOA classification in full")),"")

I need it to ignore blank cells in Column A and then return "Please...." in non blank cells which haven't been classified

It doesnt make sense to have a working spreadsheet with formulas that are overtyped with text. I presume you are suggesting that you are placing the formula in column B? Try placing the formula in column C as a 'Check' column.

It doesnt make sense to have a working spreadsheet with formulas that are overtyped with text. I presume you are suggesting that you are placing the formula in column B? Try placing the formula in column C as a 'Check' column.

sheet 1
A B C
Account Name Balance Classification

2Debtors \$50 ........
3............. ..... ........
4......... ...... ........
5......... ...... ........

So what I am saying is I want a formula that will look at column C and should be able to tell someone to complete SCOA classification because they have a number in row 2 but no classification while ignoring the fact that row 3-5 is blank (but will obviously have the formulas copied down) to allow for cases where someone has data up to row 5 they want to enter.

This used as macro would fill in all blank cells in column C with your pleaes fill in mesage

Code:
``````Sub Test ()
LastRowColA = range( "a" & rows.count).end(xlup).row
For Each Cell in Range("C2", Range("C" & LasRowColA)) 'Sets Loops for all items in list based off column a
If Cell.offset(-1,0).value = "" Then  ' Checks column b is empty
Cell.Value = "Please complete SCOA classification in full"
End If
Next Cell
End Sub``````

For new entries on the page you could put this code on page the page that would make a pop up box appear every time a user entered a value in column b it would request them to input the classification/

Code:
``````Private Sub Worksheet_Change (ByVal Target as Range)

Dim TheClass as String

If Target.Row = 2 Then  'Only pops up input box if you enter a value into a cell in column B
Theclass = InputBox("Input SCOA Classification :") ' Pop up box asking for Classification
Target.offset(1,0).value = TheClass 'Inputs your entry into cell C
End If

End Sub``````

Replies
3
Views
232
Replies
1
Views
144
Replies
8
Views
399
Replies
3
Views
245
Replies
4
Views
97

1,219,570
Messages
6,149,043
Members
450,853
Latest member
xtiinctt

### 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.

### Which adblocker are you using?

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

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