How does Data Validation change with a formula change?

[Guest]

I have formula-driven values in named lists I am using with Data
Validation, Excel 2003. My problem: The original selected response in the
DV-assigned cell stays the same, but its formula-driven value is no longer
correct after formulas in other cells/lists create a change in the values in
my original DV list. Because the originally-selected value in the dropdown
is also generating values elsewhere, this is causing huge problems in my
program. I have hundreds of choices, and they all depend on each other. Is
there any way I can make the DV originally-selected result update
automatically to its new value (i.e. to the recalculated value in the list)
without clicking on the cell containing the dropdown and re-selecting its
equivalent new value?
If there's code for this, please explain as clearly as possible -- I'm
just a babe in that particular woods. Thanks! Brenda

 

[Debra Dalgleish]

If you're using formulas to create the items in the source list, perhaps
you can use something similar in the worksheet, so it will change when
the list changes. If you provide details on the list formulas, and
sample data, someone may be able to offer specific advice.

 

[Guest]

Thanks for the suggestion. I'm trying to develop a user-friendly program
for custom handbags. An example of the choices would be -
Single Strap #1 (Same material as handbag, i.e. leather or tapestry)
Single Straps #2 and #3 (Contrast or chain - about 10 different styles for
each)
Double Straps #1 and #2 (Same as above)
If SS#1 is chosen, and ALSO selected to be Permanent (as opposed to
Detachable), that needs to invalidate any further Single Strap selections.
The Data Validation lists for SS#2, SS#3, etc., change to a void equivalent
if the former event occurs. And, the customer may change her mind about
anything already selected and make changes throughout.
It's easy to build the lookup tables and create the DV lists and
construct formulas. But there's probably in excess of 300 choices, all
formula-driven.
My material requirements, costs, etc., are generated by the Custom
selections. But, every time a new selection is made, it needs to interact
with other calculations and change them appropriately. I need to build in a
lot of error prevention, so the end results (including my P & L !!) are
accurate.
I am beginning to believe I will have to write the whole thing in code,
but I have not been able to spend the time to teach myself yet, and hoped
there would be an interim solution, just to get it up and running.
Any ideas? Thanks, Debra. Brenda

 

[Debra Dalgleish]

If your goal is to prevent users from changing a previous selection,
perhaps you could adapt the technique in the sample workbook here:

http://www.contextures.com/excelfiles.html

Under Data Validation, look for "DV0042 - Limit Data Validation Selection"