Excel - Alternative to data validation

Question

I am actually looking for an alternative to the way I created an Excel spreadsheet. For several years I have run an NCAA basketball pool for a couple of fundraisers and to manage it, I created an Excel spreadsheet that allows people to pick teams and then when complete, I take the list of their picks and copy them into another spreadsheet that I use to track standings. It works very well. The challenge I have is that some people that download it have security issues (due to macros) and others have issues because they don't have Excel. My thought was to publish via Excel Web App but data validation does not work (and macros for that matter). Here is a sample of how the validation works:

A static list of teams (inputted by me) is on it's own sheet (hidden).

The main sheet displays the matchups based on the list of starting teams (cells locked) that I entered in the traditional NCAA tournament format.

A second sheet brings in the starting list of teams as well. This is where I use named lists for all 63 games played.

On the main sheet, data validation (based on the unique named list game identifier) is used to allow a person to pick the winner of the game.

When a team is picked, it populates a cell on the second sheet where all of the 63 named lists are.

Unique validation lists are then created based on their picks.

After the whole sheet is filled out, a list of 63 picks for the individual is on the hidden 3rd sheet. That list is then copied into the tracker.

So ultimately, I want to figure out a way to do the same exact thing the validation is doing just without the validation so I can publish it as an Excel Web App. I know this is probably a bit of a reach.

Hopefully, I painted the picture well enough. It should be noted that I am not a developer. I am more of an Excel enthusiast and have worked in IT for 17 years and have become pretty savvy with Excel.

Thanks in advance.

Answer 1

Hello,

You can't use data validation and you can't use VBA. That leaves formulas and conditional formatting.

You could do this: Remove the data validation and let users type in the values. Next to these cell(s), put a match formula that looks up the entered value in the range of valid entries. If found, no prob, if not found, display an error message or use a red color highlight.

This won't keep people from entering wrong values, but it will give them a visual indicator that wrong data has been used.

We all hope that data validation will be available soon in Web Excel.

cheers, teylyn

Answer 2

Thanks for the suggestion! I will give it a try.

Thanks,

Bob