IF statement Conundrum

kudakaswa14

New Member
Joined
Aug 29, 2014
Messages
6
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

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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")
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
So you need it altered to ignore text in "A"??

=IF(A3<>"",IF(NOT(ISNUMBER(A3)),"",IF(B3<>"","","Please complete SCOA classification in full")),"")
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
How about

=IF(COUNT(A:A)>COUNTA(B:B),"Please....","")
 
Upvote 0
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

1Bad Debt $100 expense
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
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