Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2010/2007 - How much can be done with Custom Validation?
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2010/2007 - How much can be done with Custom Validation?
On Sep 17, 11:42*am, Gord wrote:
Sounds like dependent Validation lists. See Debra Dalgeish's site for help. http://www.contextures.on.ca/xlDataVal02.html Gord Dibben * *Microsoft Excel MVP On Sat, 17 Sep 2011 09:10:55 -0700 (PDT), wrote: 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.- Hide quoted text - - Show quoted text - Well, not exactly. Debra's approach requires static lists behind each piece of the validation. I need a dynamic list that "adjusts" as users enter additional information. To do what I'm talking about with that approach, I'd have to write code that would parse each line as the user entered it, determine which state it was in, then append it to that state's list of cities. I'm looking more for a way to do something like: select StateCity.City from StateCity where StateCity.State = "CA" i.e. one master list that includes a blend of all States & Cities. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2010/2007 - How much can be done with Custom Validation?
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2010/2007 - How much can be done with Custom Validation?
Debra's approach requires static lists behind each
piece of the validation. Well, not exactly............ I guess you missed the section on creating Dynamic Lists. Try also http://www.contextures.on.ca/xlDataVal13.html or http://www.contextures.on.ca/xlDataVal15.html Gord |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2010/2007 - How much can be done with Custom Validation?
On Sep 17, 5:22*pm, Gord wrote:
Debra's approach requires static lists behind each piece of the validation. Well, not exactly............ I guess you missed the section on creating Dynamic Lists. Try alsohttp://www.contextures.on.ca/xlDataVal13.html or * *http://www.contextures.on.ca/xlDataVal15.html Gord I did, indeed. On second pass, I'm still not SURE it's what I'm looking for, but I've at least got something on which to proceed until the next obstacle. It depends on whether I can, in fact, sort the various lists. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2010/2007 - How much can be done with Custom Validation?
I'm sure you can find some combination of methods from Debra's DV
pages that will work. Go to her Excel home site and browse through the extensive DV section. Try downloading a few of her files to get a better visual idea of how they operate on dependent, dynamic, sorted lists. Maybe even using a ComboBox to allow autocomplete. Good luck, Gord On Sun, 18 Sep 2011 13:48:09 -0700 (PDT), Bartt wrote: On Sep 17, 5:22*pm, Gord wrote: Debra's approach requires static lists behind each piece of the validation. Well, not exactly............ I guess you missed the section on creating Dynamic Lists. Try alsohttp://www.contextures.on.ca/xlDataVal13.html or * *http://www.contextures.on.ca/xlDataVal15.html Gord I did, indeed. On second pass, I'm still not SURE it's what I'm looking for, but I've at least got something on which to proceed until the next obstacle. It depends on whether I can, in fact, sort the various lists. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2010/2007 - How much can be done with Custom Validation?
On Sep 19, 2:39*am, Gord wrote:
I'm sure you can find some combination of methods from Debra's DV pages that will work. Go to her Excel home site and browse through the extensive DV section. Try downloading a few of her files to get a better visual idea of how they operate on dependent, dynamic, sorted lists. Maybe even using a ComboBox to allow autocomplete. Good luck, * * * * * * * * * * * Gord On Sun, 18 Sep 2011 13:48:09 -0700 (PDT), Bartt wrote: On Sep 17, 5:22*pm, Gord wrote: Debra's approach requires static lists behind each piece of the validation. Well, not exactly............ I guess you missed the section on creating Dynamic Lists. Try alsohttp://www.contextures.on.ca/xlDataVal13.html or * *http://www.contextures.on.ca/xlDataVal15.html Gord I did, indeed. On second pass, I'm still not SURE it's what I'm looking for, but I've at least got something on which to proceed until the next obstacle. It depends on whether I can, in fact, sort the various lists. This link has both the option (VBA + Formula) http://www.excelfox.com/forum/showth...-Only-Formulas) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Differences among VBA in Excel 2007 and 2010 | Excel Programming | |||
Differences between VBA in Excel 2007-2010 | Excel Programming | |||
Set color for chart serie in excel 2007/2010 | Excel Programming | |||
Easy way to add my custom button on Excel 2010 quick access toolbar? | Excel Programming | |||
Easy way to add my custom button on Excel 2010 quick access toolbar? | Excel Discussion (Misc queries) |