Hi! I have searched allover the web and can't seem to find a solution so I'd appreciate any guidance you can give me!
I have a series of cascading combo boxes in which the selection criteria for each successive combo box is limited based on what has been selected in previous combo boxes. I used EKIM's method found on this page http://www.mrexcel.com/forum/showthread.php?t=32&page=2
In doing this though, I seem to have encountered an instance where Office 2007 does not update the cell link. The simplified scenario I'm working with is this:
3 combo boxes
combo box 1: options 1 and 2
combo box 2 has a dynamic range which re-sizes based on what you select in Combo box 1.
if combo box 1 = 1; combo box 2 has options a, b
if you select 1= 2; combo box 2 has c
combo box 3 has a dynamic range which re-sizes based on what you select in Combo box 2.
if combo box 2 = a; combo box 3 has options: a1, a2, and a3
if combo box 2 = b; combo box 3 has options b1, b2
if combo box 2 = c; combo box 3 has options c1
it all works from the start. BUT if you select:
combo box 1 = 1
combo box 2 = b
combo box 3 = b3
THEN change to
combo box 1=2
combo box 2=c
there is no options for combo box 3, and any linked references are all #REF
The reason is that the cell link for combo box 2 doesn't change and remains 2. WHY?? What can I do to make it shift back to 1 (which it should do automatically because c is the only option and thus necessarily the 1st in the list)
I have a series of cascading combo boxes in which the selection criteria for each successive combo box is limited based on what has been selected in previous combo boxes. I used EKIM's method found on this page http://www.mrexcel.com/forum/showthread.php?t=32&page=2
In doing this though, I seem to have encountered an instance where Office 2007 does not update the cell link. The simplified scenario I'm working with is this:
3 combo boxes
combo box 1: options 1 and 2
combo box 2 has a dynamic range which re-sizes based on what you select in Combo box 1.
if combo box 1 = 1; combo box 2 has options a, b
if you select 1= 2; combo box 2 has c
combo box 3 has a dynamic range which re-sizes based on what you select in Combo box 2.
if combo box 2 = a; combo box 3 has options: a1, a2, and a3
if combo box 2 = b; combo box 3 has options b1, b2
if combo box 2 = c; combo box 3 has options c1
it all works from the start. BUT if you select:
combo box 1 = 1
combo box 2 = b
combo box 3 = b3
THEN change to
combo box 1=2
combo box 2=c
there is no options for combo box 3, and any linked references are all #REF
The reason is that the cell link for combo box 2 doesn't change and remains 2. WHY?? What can I do to make it shift back to 1 (which it should do automatically because c is the only option and thus necessarily the 1st in the list)
