Browse by Tags

Today’s author, Dany Hoter, a Product Planner for the Excel team, shares a neat trick he learned recently for creating dynamic data validation ranges. The sample file used for this blog post can be found in the attachments at the bottom of this post. The problem: Validating data entry based on hierarchical (parent child) data. The example used is regions and countries but it could be countries and cities, product categories and sub-categories, class and student name, etc. You want to enter a region...

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 . Read More...