Combo Box Index Number Problems: refresh & duplication

Dear Excel Forum,

I have two problems occurring with Combo Box drop down lists created using the Format Control.

1. Though the next adjacent Combo Box drop down list automatically updates correctly in accordance with the selected item in the preceding list, the corresponding index number of the adjacent Combo Box list does not, despite being assigned the correct cell link.

For example (please refer to the attached worksheet),
If you select Flip in Drop Down 2, then you'll see the two correct options in Drop Down 3 and the corresponding correct index number at E2.
Now change the Effect to 3D, and the only option in Drop Down 3 is "Turn Left-to-Right" which is correct, BUT the index number at E2 has not updated to "1" and thus returns the wrong text at E28 (red).

2. Since the index number is automatically assigned by the selected item's position in the drop down list, Drop Down 3 (and 4) is presenting a problem whereby the same index number is returned for different name lists.

For example,
Animation has only one corresponding option in Drop Down 3, i.e. Tilt Top-to-Bottom.
3D has only one corresponding option in Drop Down 3, i.e. Turn Left-to-Right.
Both would return the same index number of 1, so I've included a blank cell in TopToBot name list so that they return different numbers, but the blank position still assumes a value of 1 and returns the wrong value in cell E28 (red). Ideally I want to avoid the blank cell and the problems it brings.


Could someone kindly please offer suggestions to my problems?
I have a very basic understanding of Excel and don't understand code so if your answer includes it please explain thoroughly how I'm to implement it.

Thank you.

Bart
(Excel Mac 2004)

Explanation of Worksheet.
Outcomes required for the given size 51 W x 89 H mm in terms of Effect > Card Direction > Stock:

Flip > Turn Left-to-Right or Top-to-Bottom (Both) > Fine or Coarse (MotionStock)

Animation > Tilt Top-to-Bottom (TopToBot) > Fine or Coarse (MotionStock)

3D > Turn Left-to-Right (LeftToRight) > Fine3D or Coarse (DepthStock)

Names in Workbook:

rngChooseEffect
Refers to: =CHOOSE(Sheet1!$A$2,Effect)

rngChooseDirection
Refers to: =CHOOSE(Sheet1!$C$2,Both,TopToBot,LeftToRight)

rngChooseStock
Refers to: =CHOOSE(Sheet1!$C$2,MotionStock,MotionStock,DepthStock)

During the interim whilst www.excelforum.com has been offline in my region and not knowing when or if it would return, I posted a revised question to another Excel forum and received the answer. (After 5 days offline www.excelforum.com is now back online, but I can see it wasn't offline for others).

http://chandoo.org/forums/topic/inde...-box-drop-down

Bart