Excel 2010/2007

(Posting to programming group because my instincts tell me code would be required...)

I implement a rather robust application that uses a large footprint of reference data. Experience has shown that during an implementation, it’s far easier for the client to review the reference data in a spreadsheet format and then upload en-masse. The data has to adhere to validation that resides in the app GUI.

We’ve got a utility to upload the spreadsheet, but it doesn’t currently include any validation. As a result, the utility spits out errors that have to be reviewed & re-processed.

Some of that error/review/reprocess could be reduced if I can add at least some validation to the spreadsheet.

Custom Validation can easily do things like checking for duplicates, but there are some more sophisticated validation schemes that I’d like to employ. I don’t know whether they’re possible w/ pure Excel Custom Validation or whether they’d require VBA.

e.g. say I’ve captured a Location list:

State City
CA San Diego
NY New York
FL Miami
CA San Francisco
CA Los Angeles
FL Jacksonville

Next, consider that I’ve moved on to contacts/people in a different set of the ref data capture & need to specify an address.

Is there a way to employ custom validation (without VBA because I don’t want to have to support VBA code in addition to app code) that would allow a user to select a State in cell A2 of the user sheet (e.g. “CA”) which in turn limits my validation list for the contact's city to only those Cities from the selected state? e.g. on contact sheet, for "State" user selects “CA” & the selection list in contact sheet for "City" becomes “San Diego”, “San Francisco”, & “Los Angeles”.

Initially, the State/City list wouldn’t be sorted, but that’s a simple enough exercise that I could justify a simple macro to do it.