IF statement Conundrum

[kudakaswa14]

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.

 

[steve the fish]

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")

 

[tyger0951]

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.

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

 

[kudakaswa14]

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.

 

[steve the fish]

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

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

 

[kudakaswa14]

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

 

[steve the fish]

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.

 

[jasonb75]

How about

=IF(COUNT(A:A)>COUNTA(B:B),"Please....","")

 

[kudakaswa14]

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.

 

[tyger0951]

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

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/

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