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 didnt 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)). Couldnt 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")))) (dont
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 couldnt 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 cant work out how to write them, so€¦€¦€¦€¦€¦€¦ could someone please
help me with a simpler solution. BTW using Excel 2003.

Cheers
Lynda

Hi Lynda

If you want to mail me a copy of your workbook, I will see if I can sort it
out for you.
To mail direct, send to
roger at technology4u dot co dot uk
Change the at and dots to make valid email address

--
Regards
Roger Govier

"Lynda" wrote in message
...
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 didnt 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)). Couldnt 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")))) (dont
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 couldnt 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 cant work out how to write them, so€¦€¦€¦€¦€¦€¦ could someone
please
help me with a simpler solution. BTW using Excel 2003.

Cheers
Lynda

Thank you Roger, i will get it ready to send now.

Cheers
Lynda

"Roger Govier" wrote:

Hi Lynda

If you want to mail me a copy of your workbook, I will see if I can sort it
out for you.
To mail direct, send to
roger at technology4u dot co dot uk
Change the at and dots to make valid email address

--
Regards
Roger Govier

"Lynda" wrote in message
...
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 didnt 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)). Couldnt 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")))) (dont
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 couldnt 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 cant work out how to write them, so€¦€¦€¦€¦€¦€¦ could someone
please
help me with a simpler solution. BTW using Excel 2003.

Cheers
Lynda

Thank you so much Roger. Roger has resolved my problem using VBA to write the
data from the input form to the data sheet with a button click.

Cheers
Lynda

"Roger Govier" wrote:

Hi Lynda

If you want to mail me a copy of your workbook, I will see if I can sort it
out for you.
To mail direct, send to
roger at technology4u dot co dot uk
Change the at and dots to make valid email address

--
Regards
Roger Govier

"Lynda" wrote in message
...
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 didnt 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)). Couldnt 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")))) (dont
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 couldnt 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 cant work out how to write them, so€¦€¦€¦€¦€¦€¦ could someone
please
help me with a simpler solution. BTW using Excel 2003.

Cheers
Lynda