Excel validation Sheet

**venkateshsql** · 08-22-2011, 03:37 PM

Hi

I am trying to create a validation excel sheet for the business users to make sure they are at least keying data in the sales sheet required fields also check for duplicates in the sales sheet.

My sales spreadsheet looks something like this.

Transaction Id, Transaction Amount, Transaction Date, Account, Account Type etc

The basic idea for this validation spreadsheet is to validate data in the Sales spreadsheet as this is manually edited by sales team. They sometimes forget to save their changes or key nothing and have many blank cells, which in turn screws the ETL load to the database throwing errors. For instance "Sales" table cannot have NULL value in the column "Account".

My question is how do I develop this validation spreadsheet which validates against sales spreadsheet.

Thanks

**jaslake** · 08-22-2011, 03:55 PM

Hi venkateshsql

Welcome to the Forum!

First things that come to mind is a Workbook Before Close event and/or a Workbook Before Save Event. This event(s) would check all the required fields for the requirements.

Were it me, the process I'd explore is using a UserForm for the data entry. If you're using a UserForm for your data entry the checking can be done in the UserForm itself and not allow required data to be omitted.

Let me know how I can help.

**venkateshsql** · 08-22-2011, 04:24 PM

Thanks for your response. Well, data is keyed in manually in the spreadsheet no user forms being used.

Regards
Venkatesh

**jaslake** · 08-22-2011, 07:54 PM

Hi Venkatesh

I'd recommend a UserForm approach to your issue as it simplifies things significantly. Is this call within your purview?

If not we can look at a Workbook Before Close event and/or a Workbook Before Save Event.

In either case, I'd like to see what your data looks like.