Dependant Drop Down lists in two corresponding cells

Hi,

I'm sure this is probably quite a simple thing to do but I just can't get my head round it. I would like to create two independent cells with drop down lists that are linked to each other and will change depending on which item I choose from which cell list.

As an example:

Cell A4 has a drop down list: 1, 2, 3, 4, 5, 6, 7, 8, 9

Cell B4 has a drop down list: A, B, C, D, E, F, G, H, I

If I choose number '1' from the drop down in cell A4 I want cell B4 to automatically select 'A'. But I also want it to work in reverse so if I choose the letter 'D' from cell B4 I would like cell A4 to automatically change to '4'

The drop down list is easy and I know how to do that but I don't know how to add a formula to link the two cells in both directions as well as keep the drop down lists.

Does that make sense and can anyone help?

Thanks

Originally Posted by Mattnewbie

don't know how to add a formula to link the two cells in both directions as well as keep the drop down lists.

No body knows. It could be done only by macro.