I have list of products that are tied to rates.
one product has 5 rates and the second has 10 rates.
The product is chosen by a drop down box
I have a lookup to create a table with the rates- this is range named "rates".
I have a drop down box linked to "rates" so a customer can choose the
product and the rate with drop down boxes.

My issue is that if the product with 5 rates is chosen then you have a 5
blank rows in the rate drop down box. Is there any way to link the drop down
to 2 different range names - based on product - or is there some better way
to do this ?

Thanks,
Yosef

Hi!

Say your product drop down has choices of:

Product1
Product2
Product3
etc

You need to create a rate table for each Product and name each table as the
product that it corresponds to.

Say A1 is the product drop down and B1 is the rate drop down.

As the source for the drop down in B1 use:

=INDIRECT(A1)

So, if Product1 was selected from the drop down in cell A1 then the drop
down in B1 will use the named range Product1 as the source for the rates
that correspond to product1.

Biff

"ynissel" wrote in message
...
I have list of products that are tied to rates.
one product has 5 rates and the second has 10 rates.
The product is chosen by a drop down box
I have a lookup to create a table with the rates- this is range named
"rates".
I have a drop down box linked to "rates" so a customer can choose the
product and the rate with drop down boxes.

My issue is that if the product with 5 rates is chosen then you have a 5
blank rows in the rate drop down box. Is there any way to link the drop
down
to 2 different range names - based on product - or is there some better
way
to do this ?

Thanks,
Yosef

Biff,
I tried to implement your suggestion. But when I go to the properties of my
combobox - and try to input =indirect(whichrate) (I named the lookup
whichrate) it doesnt take. The line just goes blank. Any ideas ?
Thanks,
Yosef

"Biff" wrote:

Hi!

Say your product drop down has choices of:

Product1
Product2
Product3
etc

You need to create a rate table for each Product and name each table as the
product that it corresponds to.

Say A1 is the product drop down and B1 is the rate drop down.

As the source for the drop down in B1 use:

=INDIRECT(A1)

So, if Product1 was selected from the drop down in cell A1 then the drop
down in B1 will use the named range Product1 as the source for the rates
that correspond to product1.

Biff

"ynissel" wrote in message
...
I have list of products that are tied to rates.
one product has 5 rates and the second has 10 rates.
The product is chosen by a drop down box
I have a lookup to create a table with the rates- this is range named
"rates".
I have a drop down box linked to "rates" so a customer can choose the
product and the rate with drop down boxes.

My issue is that if the product with 5 rates is chosen then you have a 5
blank rows in the rate drop down box. Is there any way to link the drop
down
to 2 different range names - based on product - or is there some better
way
to do this ?

Thanks,
Yosef

Hi

Take a look at

http://www.contextures.com/xlDataVal02.html



"ynissel"
wrote in message ...

I have list of products that are tied to rates.
one product has 5 rates and the second has 10 rates.
The product is chosen by a drop down box
I have a lookup to create a table with the rates- this is range named
"rates".
I have a drop down box linked to "rates" so a customer can choose the
product and the rate with drop down boxes.

My issue is that if the product with 5 rates is chosen then you have a 5
blank rows in the rate drop down box. Is there any way to link the drop
down
to 2 different range names - based on product - or is there some better way
to do this ?

Thanks,
Yosef

KK - The problem is I used Combo boxes not validation. Any ideas on doing it
this way ?
Thanks.

"kk" wrote:

Hi

Take a look at

http://www.contextures.com/xlDataVal02.html



"ynissel"
wrote in message ...

I have list of products that are tied to rates.
one product has 5 rates and the second has 10 rates.
The product is chosen by a drop down box
I have a lookup to create a table with the rates- this is range named
"rates".
I have a drop down box linked to "rates" so a customer can choose the
product and the rate with drop down boxes.

My issue is that if the product with 5 rates is chosen then you have a 5
blank rows in the rate drop down box. Is there any way to link the drop
down
to 2 different range names - based on product - or is there some better way
to do this ?

Thanks,
Yosef

Hi!

Make things easy on yourself and use data validation drop downs!

Biff

"ynissel" wrote in message
...
Biff,
I tried to implement your suggestion. But when I go to the properties of
my
combobox - and try to input =indirect(whichrate) (I named the lookup
whichrate) it doesnt take. The line just goes blank. Any ideas ?
Thanks,
Yosef

"Biff" wrote:

Hi!

Say your product drop down has choices of:

Product1
Product2
Product3
etc

You need to create a rate table for each Product and name each table as
the
product that it corresponds to.

Say A1 is the product drop down and B1 is the rate drop down.

As the source for the drop down in B1 use:

=INDIRECT(A1)

So, if Product1 was selected from the drop down in cell A1 then the drop
down in B1 will use the named range Product1 as the source for the rates
that correspond to product1.

Biff

"ynissel" wrote in message
...
I have list of products that are tied to rates.
one product has 5 rates and the second has 10 rates.
The product is chosen by a drop down box
I have a lookup to create a table with the rates- this is range named
"rates".
I have a drop down box linked to "rates" so a customer can choose the
product and the rate with drop down boxes.

My issue is that if the product with 5 rates is chosen then you have a
5
blank rows in the rate drop down box. Is there any way to link the
drop
down
to 2 different range names - based on product - or is there some better
way
to do this ?

Thanks,
Yosef

Thanks alot - but for the format of the form I really need the check box.
validation is too limiting.

"Biff" wrote:

Hi!

Make things easy on yourself and use data validation drop downs!

Biff

"ynissel" wrote in message
...
Biff,
I tried to implement your suggestion. But when I go to the properties of
my
combobox - and try to input =indirect(whichrate) (I named the lookup
whichrate) it doesnt take. The line just goes blank. Any ideas ?
Thanks,
Yosef

"Biff" wrote:

Hi!

Say your product drop down has choices of:

Product1
Product2
Product3
etc

You need to create a rate table for each Product and name each table as
the
product that it corresponds to.

Say A1 is the product drop down and B1 is the rate drop down.

As the source for the drop down in B1 use:

=INDIRECT(A1)

So, if Product1 was selected from the drop down in cell A1 then the drop
down in B1 will use the named range Product1 as the source for the rates
that correspond to product1.

Biff

"ynissel" wrote in message
...
I have list of products that are tied to rates.
one product has 5 rates and the second has 10 rates.
The product is chosen by a drop down box
I have a lookup to create a table with the rates- this is range named
"rates".
I have a drop down box linked to "rates" so a customer can choose the
product and the rate with drop down boxes.

My issue is that if the product with 5 rates is chosen then you have a
5
blank rows in the rate drop down box. Is there any way to link the
drop
down
to 2 different range names - based on product - or is there some better
way
to do this ?

Thanks,
Yosef

Hi,

There are lot of examples in MrExel

http://www.mrexcel.com/archive/Controls/index.html


Try to look at this. It might help you.

http://www.mrexcel.com/archive/Controls/30062.html

kk


"ynissel" wrote in message
...
KK - The problem is I used Combo boxes not validation. Any ideas on doing
it
this way ?
Thanks.

"kk" wrote:

Hi

Take a look at

http://www.contextures.com/xlDataVal02.html



"ynissel"
wrote in message
...

I have list of products that are tied to rates.
one product has 5 rates and the second has 10 rates.
The product is chosen by a drop down box
I have a lookup to create a table with the rates- this is range named
"rates".
I have a drop down box linked to "rates" so a customer can choose the
product and the rate with drop down boxes.

My issue is that if the product with 5 rates is chosen then you have a 5
blank rows in the rate drop down box. Is there any way to link the drop
down
to 2 different range names - based on product - or is there some better
way
to do this ?

Thanks,
Yosef