Excel 2010-What method to use to identify info missing or in error?

[EvanDef]

Hello Excel Gurus!

Thanks to anyone who can point me in the right direction.

I have been tasked to create a "smart" data collection form in excel 2010 where a user inputs collected info from a call. The form is divided into 5 main sections with as few as 6 and as many as 26 manually input variables in each section. The variables are either simple 2 to 4 category validation tables or manual text entry. The smart aspect would be for Excel to validate the required variables within the section to determine if the form was completed properly and then display a statement at the end of the section advising accuracy ir error.

I started a formula from the "accuracy statement" field of a section with 14 variables. The nested IF formula is quite long and continues to give me errors and FALSE readings.

Am I going about this the wrong way, is there a better solution? I hope I have provided enough information, if not I can add what may be missing.

Thanks for your time,Ii appreciate anyone who can lend a hand!

Evan

 

[KB_Miner]

Have you considered using a VBA macro to test instead? Perhaps could use a Select Case to test for the required fields and then tie the macro to a button they can hit to verify all fields were entered.

 

[EvanDef]

Hello KB, Thanks for the reply. I did fear that was the answer I was going to get, as I am not that strong with VBA just yet. I do have two copies of the template. I have been playing with formula's and VBA in one and then move the working application over to the master template. Need to strengthen my VBA skills.

 

[KB_Miner]

If you're still learning VBA, then perhaps a longer If Then statement might be a good place to start. For instance, if Name was in A2 and Address in B2, you could do something like this:

If ISBLANK(Range("A2")) = True Then
msgBox "Name not entered"
ElseIf ISBLANK(Range("B2")) = True Then
msgBox "Address not entered"
End If