Hi all,


The title is what I am currently trying. I tried some other options and it wasn't robust enough (at least with me designing).

That being said if there is a better (more stable, faster, elegant, etc) solution that what I am trying please let me know



The Scenario:


On Worksheet("Catalog") (Hidden)

I have the catalog of data that I want select/copy from.

The Catalog has a lot of data that isn't so relevant to the question...
but a disclaimer: Where the final selection copies in the "model" and "unit price" I would also want to copy some other data of relevance to the user, but not relevant to the selection process.



On Worksheet("Selector")
Tag Qty State (hidden) Option1 Option2 Option3 Option4 Option5 Model Unit Price Extended Price
whatever 1 or more Hidden state Dropdown1 Dropdown2 Dropdown3 Dropdown4 Or Autofilled by 5 Dropdown5 Or Autofilled by 4 Autofilled Autofilled Calculated Cell

Option1 is initially populated with 3 options.

Option2 is dependent on what is selected in Option1.

Option3 is dependent on what is selected in Option2.

(Note: this is as far as I could get when using Named Ranges and Index(Match) formulas.)

I am using the State column to track the selection process and reset Dependent Options if an earlier cell is changed.
Basically: If a dependent cell is already selected the "state" knows it. So if you modify a cell that changes the possible options for the dependent cell it clears the existing value in the dependent cell and writes the new Validation list for the dependent cell.

Option4 and Option5 are dependent on what is selected in Option1, Option2, and Option3. The information in Option4 and Option5 are directly correlated (ie if Option4 is "250" then Option5 is always "590"), but I would like to select by either. In the event the users selects via Option4 then the Option5 box is Autofilled. If Option5 is selected then Option4 is Autofilled.

Model and Unit price should be autofilled once the cells are filled in to select only 1 specific row. They will have to be cleared if the cells they are dependent on are modified.

(Note: I started trying AutoFilter criteria1,2,3,etc, then copying SpecialCells(xlCellTypeVisible) into an array, then using a for loop to write the selection to the .Validation lists for Option4/5 but I got concerned that this would slow things down in the WS_Change events.)

Currently I have a fairly complex nesting of If/ElseIf/Else and For loops on the WS_Change event. Part of the complexity stems from trying to make sure that any currently unneeded code isn't run on every change. (ie if Option2 isn't selected then don't run code for 3,4,5 on that row.) That being said the total lines is only at 199 with a lot of it being white-space and comments.

I have been running a Start_time End_time routine and it doesn't seem to bad on performance so far.


So what I am currently stuck on/meditating on is how to best fill options4,5,model, and unit price.

I was thinking I could save the Worksheet("Catalog").Range() to an array (CatArr) and then for each change event that requires the final look up I would copy the array (CopyCat = CatArr) and then for-loop CopyCat to match the selection from Option1,2,3. Then for-loop that selection into Option4,5. Then I would need another for-loop to select the final model,unit price, (and other data).


I am concerned that I have backed myself into a certain way of thinking, and that there might be a better way to accomplish this.

I have been looking at it too long and would appreciate anything that outside wisdom has to offer

Thanks!