• Data Validation in Excel Services

    I’m a huge consumer of Data Validation in Excel. At this point, however, Excel services (the Excel webApp) is still so new that there are very few of the techniques that we normally use in Excel which are web compliant. This article looks at ways that we can implement data validation into an Excel services solution.

    Data Validation techniques I use when building workbooks include:

    Pro-active Data Validation:
    • Excel’s Data Validation toolset
    • Form controls
    • ActiveX controls
    • PivotTable filters
    • Slicers

    Re-active Data Validation:
    • Conditional formats
    • Contextual formulas (IF)

    So naturally, as I was trying to convert one of my web pages to use Microsoft WebApp off SkyDrive, I ran into issues. This is frustrating since, if we’re trying to build a web application, sanitizing the data is really important to make sure it works correctly.

    I decided to convert my “Automation Evaluation” worksheet, which was intended to display how much it cost someone to do repetitive tasks over time. The overall goal of this file was to convince someone that they should pay me to automate their work, back when I was still doing consulting projects. I don’t do them any more, but I think the exercise is still worth having on the site.

    In this file, it was really important to me to control the options people can select to work with for how many minutes/hours/days they spend on a task. I want the right data in there to drive my chart, yet my options are VERY limited in WebApp.

    I tried the slicers, but they aren’t really built for this scenario. Set horizontally or vertically they take up way too much space. You can set them to a single column then make your user scroll down, but if you only have one option on the screen (like a combo box) then it gets awkward to figure out which option(s) are selected, and involves extra clicks. And that doesn’t even touch the “how do I return the clicked value from a slicer” issue. (I haven’t figured out how to get a value from a non-PowerPivot slicer yet.)

    So this time I reached to PivotTables. I ended up making some very small tables with the options I needed and created a PivotTable that uses only the Page Field. This give me the ability to get a drop-down with pre-defined options. It works, but it does have a couple of issues:

    • In the client it defaults to a “Select one item” approach and you have to check a box to allow you to select multiple items. In WebApp, it allows you to select multiple items. So I had to add a contextual formula to feed back info to the users if this happens.
    • There are actually hidden rows in the worksheet to allow the Page Field to be changed. Even though the options selected always end up in the page field line, it seems to need extra space to refresh the table.

    At any rate, it’s not perfect, but it seems to work. Here’s a look at the file:


    Quote Originally Posted by Claire
    on March 9th, 2011 at 8:23 am

    Looks good to me–pivot tables are genius. How do you like SkyDrive? I’ve heard nice things
    The biggest issue with using PivotTables for this is that in the web app you can’t restrict the user to only selecting one item. So they can quickly mess up the sheet by selecting two items from the list. Hence resorting to the IF function to tell the user if something goes wrong.

    I think SkyDrive has lots of potential. For this kind of stuff it works quite well, although I do wish I had the ability to prevent a full download.
    Quote Originally Posted by Christopher Fennell
    on August 4th, 2011 at 12:34 pm

    Great post.

    I have been ubale to get SkyDrive to work for me. I tried searching Goolge, but I cannot find a reason. This post helped, but I still have more to learn I guess.

    Thank you,

    Christopher
    Hi Cristopher,

    What issues are you having? And what browser do you use?
    Quote Originally Posted by Homer Shannon
    on October 27th, 2011 at 11:25 am

    Why is it that if you upload an Excel spread sheet to Skydrive, it only uploads the data, not the functions. This makes it pretty useless for spread sheets that have any sophistication. (Unless you re-write all the formulas after uploading.)
    Ken Puls said,


    on October 27th, 2011 at 11:49 am

    Uh… I can’t say that I follow… I’ve had very few functions stripped, and those only if they weren’t supported at all (so a re-write wouldn’t help.) What kind of functions are you losing?

     

    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!

     

    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

  • MVP Logo
  • Recent Forum Posts

    hbgurley

    Help With SumIfs

    Your formula in column O3 was very close. The problem was that your named ranges were different lengths. WeekNo went from A2:A69 while Worker and TimeTaken...

    hbgurley Today, 08:53 PM Go to last post
    Ken Puls

    Acess data in shared onedrive file

    Hi Sam,

    When you open the file, what gets returned if you go to File -> Info -> Copy Path?

    Paste that in a cell. I can't...

    Ken Puls Today, 07:29 PM Go to last post
    Ken Puls

    Power Query returning Excel functions

    Hi jmlee,

    It's entirely possible to create formulas in Power Query, but you have to learn the M syntax, which is quite different than Excel's...

    Ken Puls Today, 07:14 PM Go to last post
    sjo007

    Conditional Formatting based on a criteria

    I have a row of values and a Standard Deviation calculation at the end of each row. I have a cell on the same sheet with an SD number in.

    ...

    sjo007 Today, 03:56 PM Go to last post
    Currymonster30

    Help With SumIfs

    Hi I wonder if anyone can help as I’m not an excel expert and am stuck.
    I’ve entered some dummy data into my workbook to test it but can’t figure...

    Currymonster30 Today, 03:31 PM Go to last post