Back
Excel

Example file for PivotTable / Data Validation Trick

Recently I posted an article discussing how PivotTables can be used on Excel Services to mimick the Data Validation feature.  The author, Dany Hoter, sent me a copy of the file he used in the example screen shots and I never got around to posting it for our readers.  Here’s a short description of the example file from Dany:

The example implements a scenario of calculating the pricing for car insurance based on the details entered in a form. The form can be collected on paper and entered manually into Excel or collected online and fed into Excel services to perform the calculation.

Each value is connected to a numeric value. High values means less risk to insure this car to this driver. More driving experience, safer car, better neighborhood etc. Of course all data is completely fake and does not represent any real data.

Each field might have a different number of possible values, some have only Yes/No and some might have 10 different values. All list of values where extended to the last row so to avoid blank appearing as an option for input. We can’t really prevent the user from choosing multiple options or the all option so an error message is displayed when the user does that.

The file can be downloaded from here.