L
Lynda
I have triple dependant dropdowns using the Combo Boxes from the Forms
toolbox.
I have a data sheet that collects the information from the dropdowns on
sheet 1. Unfortunately the dropdowns return the cell/row number instead of
the text from the list. I was given,
=OFFSET(Sheet2!J1,'Dropdowns'!G35-1,0,1,1), to revert the cells from numbers
to text. This worked fine on the DD1 but didn’t work on the next two
dropdowns. Well not quite right, it worked when I chose the text at # 1 in
DD1 it would allow me to choose the appropriate selection in DD2 that
corresponded with DD1 and in turn would allow me to choose the #1 choice in
DD3. Dropdown 2 is dependant on DD1 and DD3 is dependant on DD2. A lovely man
called (patient) Dave Peterson wrote the code for my dropdowns, bless his
heart. I digress, anyway, when I choose #2 in DD1it would still give me the
selections appropriate to DD1for DD2 and DD3. So I was given
=INDIRECT(OFFSET(Lists!J1,'Dropdowns'!G35-1,0,1,1)). Couldn’t get it to work
either. So then I went to =IF(AND(B3=2, C3=2,"CTOD"),IF(AND(B3=2,
C3=3,"ICTDD"),IF(AND(B3=2, C3=4,"ICTOD"),IF(AND(B3=2, C3=5,"OCIO")))) (don’t
be critical of this piece of formula, wrote it in a hurry) it worked fine for
the first argument but then when I started to write it for the data coming
from DD3 I couldn’t use it because it was too long and I had to start
breaking it up and I ended up having to write more formula to gather in the
others. Huge, massive, had enough, I even tried to write myself a VB script
but I am even more hopeless at that, I can see how they work once they are
written but I can’t work out how to write them, so……………… could someone please
help me with a simpler solution. BTW using Excel 2003.
Cheers
Lynda
toolbox.
I have a data sheet that collects the information from the dropdowns on
sheet 1. Unfortunately the dropdowns return the cell/row number instead of
the text from the list. I was given,
=OFFSET(Sheet2!J1,'Dropdowns'!G35-1,0,1,1), to revert the cells from numbers
to text. This worked fine on the DD1 but didn’t work on the next two
dropdowns. Well not quite right, it worked when I chose the text at # 1 in
DD1 it would allow me to choose the appropriate selection in DD2 that
corresponded with DD1 and in turn would allow me to choose the #1 choice in
DD3. Dropdown 2 is dependant on DD1 and DD3 is dependant on DD2. A lovely man
called (patient) Dave Peterson wrote the code for my dropdowns, bless his
heart. I digress, anyway, when I choose #2 in DD1it would still give me the
selections appropriate to DD1for DD2 and DD3. So I was given
=INDIRECT(OFFSET(Lists!J1,'Dropdowns'!G35-1,0,1,1)). Couldn’t get it to work
either. So then I went to =IF(AND(B3=2, C3=2,"CTOD"),IF(AND(B3=2,
C3=3,"ICTDD"),IF(AND(B3=2, C3=4,"ICTOD"),IF(AND(B3=2, C3=5,"OCIO")))) (don’t
be critical of this piece of formula, wrote it in a hurry) it worked fine for
the first argument but then when I started to write it for the data coming
from DD3 I couldn’t use it because it was too long and I had to start
breaking it up and I ended up having to write more formula to gather in the
others. Huge, massive, had enough, I even tried to write myself a VB script
but I am even more hopeless at that, I can see how they work once they are
written but I can’t work out how to write them, so……………… could someone please
help me with a simpler solution. BTW using Excel 2003.
Cheers
Lynda