Issue with dependent combo box when one 'content list' is shorter than others

[aussiecleo]

Hi

I have been wrangling with this issue and Googling madly, but cannot seem to find an answer on this.

I have set up 2 combo boxes where the second depends on the first. My (sample) spreadsheet has 2 tabs: "Reference" and "Formula"

I have 3 columns of data to be used - Assignee, Priority and Phase.
- There are 10 assignees in the Assignee list (column A)
- There are 3 priorities in the Priority list (columns B)
- There are 4 phases in the Phase list (column C).

In column E I have the 'Criteria' for the first combo box (Assignee, Priority and Phase) in cells E2:E4
In column F is the cell link for the first combo box (F2)
Column G is the link for the second combo box (G2)
Column H is Content to populate the second combo box. Formula in H2 is "=Index A2:C2,,$F$2" and this is copied down to H11. So when I select Assignee (for example), all 10 assignees are listed in cells H2:H11 etc.

For the first combo box, the input range is is $E$2:$E$4 and cell link is $F$2
For the seconc combo box, I followed one of Leila Gharani's tutorials to make sure that there are not a ton of 'blanks' at the bottom of the combo boxes, and therefore the 'input range' is "combobox" (which I defined in Name Manager as follows)
"=Formula!$H$2:INDEX(Formula!$H$2:$H$16,MATCH("zzzzzzz",Formula!$H$2:$H$16,1))"
and the cell link is $G$2.

I also have 2 cells set up so that the output of what is selected in the box can be queried: O2 ("=INDEX($E2:$E4,$F$2)) and O5 ("=INDEX($H$2:H11,$G$2)

Summary of issue: When I select Assignee in the first combo box, this populates the second combo box with10 items. If I select the last one (Assignee J) in the second combo box, then go back to the first combo box and select one of the 'shorter' criteria that has less possible values - eg Priority, whilst the second combo box shows "Medium" cell G2 still shows "9" (which is the reference to what was previously picked, ie Assignee J). Cell O2 (where I want to grab the selection to use in a formula) shows "0". If I then pick say "High", G2 updates to 1 as expected. But if I really did want Medium (the one that landed at default) - even if I re-select it in the second combo box, it doesn't register that it's been selected, and G2 remains as 9. I know I can 'force' the correct value of G2 by going and selecting High, then going back and selecting Medium, but this is not user friendly if someone else is using the sheet.

Is there something I can do under this circumstance?

Thanks in advance, btw this is my first post so I hope I have provided enough context and explanation.