Hi,

I have two cells with dropdowns in them - cells A2 and B2. In A2 I used the
data validation to only allow my salespeople to be selected and now I want to
make a validation in cell B2 that will only allow the products that my
salesperson is allowed to sell.

I have a table that lists all of the salespeople multiple times in one
column and then the product that they can sell listed in the next column
(each on its own row). For example:

Mike Balls
Mike Racquets
Mike Strings
Frank Strings
Frank Wristbands
etc.

Is it possible for the data validation in cell B1 to look into cell A1 and
then look into the table to find which products the salesperson is allowed?

Any help is greatly appreciated.

Thanks,
Michael

Assuming your table is sorted by salesperson as is shown in your sample.

Assume the table is in the range G1:H5.

As the source for the drop down in B1 use this formula:

=OFFSET(H1,MATCH(A1,G1:G5,0)-1,,COUNTIF(G1:G5,A1))

If A1 is empty when you set this up you'll get a message that says something
like: the source currently evaluates to an error...do you want to continue?
Just answer YES to this message.

Something to consider...

If you select Mike and Balls then you change it and select Frank, Balls is
still selected in B1 and this is not a valid selection for Frank.


--
Biff
Microsoft Excel MVP


"MichaelR" wrote in message
...
Hi,

I have two cells with dropdowns in them - cells A2 and B2. In A2 I used
the
data validation to only allow my salespeople to be selected and now I want
to
make a validation in cell B2 that will only allow the products that my
salesperson is allowed to sell.

I have a table that lists all of the salespeople multiple times in one
column and then the product that they can sell listed in the next column
(each on its own row). For example:

Mike Balls
Mike Racquets
Mike Strings
Frank Strings
Frank Wristbands
etc.

Is it possible for the data validation in cell B1 to look into cell A1 and
then look into the table to find which products the salesperson is
allowed?

Any help is greatly appreciated.

Thanks,
Michael

Biff,

Thank you for your help. The formula worked very well but unfortunately data
validation wasn't the tool that I should have used to achieve what I wanted
to do. Could you give me some suggestions for other ways in which I can do
the following:

I have a table that summarizes all of my past sales data for each of my
reps. The columns look like this:

RepNum Product State Months (1) Months (2)... Months (12) Total
20 XYZ NM $150 $700 .... $50
Sum(Months)
20 XYZ AZ $200 $900 .... $100
20 XYZ NV $150 $0 .... $0
....

The problem is that there are some entries that were mistakes because a
particular sales rep doesn't sell in a certain state. In the example above,
rep 20 doesn't really sell in NV so that $150 are a faulty entry and so I
want the total column to show $0 and not $150.

The idea that i had earlier was to insert a column after state with the data
validation formula that you gave me before and then put a formula in the new
column that would copy the state from the cell that was on the same row in
the previous column. I thought that for all the states that were not valid,
the cell would display an #N/A but that wasn't the case.

I really need to finish working on this data before the weekend and so far
I've been doing it manually. Any help that you can spare would be hugely
appreciated.

Thanks,
Michael


"T. Valko" wrote:

Assuming your table is sorted by salesperson as is shown in your sample.

Assume the table is in the range G1:H5.

As the source for the drop down in B1 use this formula:

=OFFSET(H1,MATCH(A1,G1:G5,0)-1,,COUNTIF(G1:G5,A1))

If A1 is empty when you set this up you'll get a message that says something
like: the source currently evaluates to an error...do you want to continue?
Just answer YES to this message.

Something to consider...

If you select Mike and Balls then you change it and select Frank, Balls is
still selected in B1 and this is not a valid selection for Frank.


--
Biff
Microsoft Excel MVP


"MichaelR" wrote in message
...
Hi,

I have two cells with dropdowns in them - cells A2 and B2. In A2 I used
the
data validation to only allow my salespeople to be selected and now I want
to
make a validation in cell B2 that will only allow the products that my
salesperson is allowed to sell.

I have a table that lists all of the salespeople multiple times in one
column and then the product that they can sell listed in the next column
(each on its own row). For example:

Mike Balls
Mike Racquets
Mike Strings
Frank Strings
Frank Wristbands
etc.

Is it possible for the data validation in cell B1 to look into cell A1 and
then look into the table to find which products the salesperson is
allowed?

Any help is greatly appreciated.

Thanks,
Michael

Well, I'm not really following you on this.

I don't see where you have 150 in a total column.

If NV is an invalid entry how can someone tell that?

--
Biff
Microsoft Excel MVP


"MichaelR" wrote in message
...
Biff,

Thank you for your help. The formula worked very well but unfortunately
data
validation wasn't the tool that I should have used to achieve what I
wanted
to do. Could you give me some suggestions for other ways in which I can do
the following:

I have a table that summarizes all of my past sales data for each of my
reps. The columns look like this:

RepNum Product State Months (1) Months (2)... Months (12) Total
20 XYZ NM $150 $700 .... $50
Sum(Months)
20 XYZ AZ $200 $900 .... $100
20 XYZ NV $150 $0 .... $0
...

The problem is that there are some entries that were mistakes because a
particular sales rep doesn't sell in a certain state. In the example
above,
rep 20 doesn't really sell in NV so that $150 are a faulty entry and so I
want the total column to show $0 and not $150.

The idea that i had earlier was to insert a column after state with the
data
validation formula that you gave me before and then put a formula in the
new
column that would copy the state from the cell that was on the same row in
the previous column. I thought that for all the states that were not
valid,
the cell would display an #N/A but that wasn't the case.

I really need to finish working on this data before the weekend and so far
I've been doing it manually. Any help that you can spare would be hugely
appreciated.

Thanks,
Michael


"T. Valko" wrote:

Assuming your table is sorted by salesperson as is shown in your sample.

Assume the table is in the range G1:H5.

As the source for the drop down in B1 use this formula:

=OFFSET(H1,MATCH(A1,G1:G5,0)-1,,COUNTIF(G1:G5,A1))

If A1 is empty when you set this up you'll get a message that says
something
like: the source currently evaluates to an error...do you want to
continue?
Just answer YES to this message.

Something to consider...

If you select Mike and Balls then you change it and select Frank, Balls
is
still selected in B1 and this is not a valid selection for Frank.


--
Biff
Microsoft Excel MVP


"MichaelR" wrote in message
...
Hi,

I have two cells with dropdowns in them - cells A2 and B2. In A2 I used
the
data validation to only allow my salespeople to be selected and now I
want
to
make a validation in cell B2 that will only allow the products that my
salesperson is allowed to sell.

I have a table that lists all of the salespeople multiple times in one
column and then the product that they can sell listed in the next
column
(each on its own row). For example:

Mike Balls
Mike Racquets
Mike Strings
Frank Strings
Frank Wristbands
etc.

Is it possible for the data validation in cell B1 to look into cell A1
and
then look into the table to find which products the salesperson is
allowed?

Any help is greatly appreciated.

Thanks,
Michael

The 150 would appear in the total column for row 3 but I want it to show a
zero instead because that row entry is a faulty one. The only way to know
that an entry is faulty is if the sale for a particular rep is in a state
that does not belong to that rep.

Sorry if wasn't clear earlier.

"T. Valko" wrote:

Well, I'm not really following you on this.

I don't see where you have 150 in a total column.

If NV is an invalid entry how can someone tell that?

--
Biff
Microsoft Excel MVP


"MichaelR" wrote in message
...
Biff,

Thank you for your help. The formula worked very well but unfortunately
data
validation wasn't the tool that I should have used to achieve what I
wanted
to do. Could you give me some suggestions for other ways in which I can do
the following:

I have a table that summarizes all of my past sales data for each of my
reps. The columns look like this:

RepNum Product State Months (1) Months (2)... Months (12) Total
20 XYZ NM $150 $700 .... $50
Sum(Months)
20 XYZ AZ $200 $900 .... $100
20 XYZ NV $150 $0 .... $0
...

The problem is that there are some entries that were mistakes because a
particular sales rep doesn't sell in a certain state. In the example
above,
rep 20 doesn't really sell in NV so that $150 are a faulty entry and so I
want the total column to show $0 and not $150.

The idea that i had earlier was to insert a column after state with the
data
validation formula that you gave me before and then put a formula in the
new
column that would copy the state from the cell that was on the same row in
the previous column. I thought that for all the states that were not
valid,
the cell would display an #N/A but that wasn't the case.

I really need to finish working on this data before the weekend and so far
I've been doing it manually. Any help that you can spare would be hugely
appreciated.

Thanks,
Michael


"T. Valko" wrote:

Assuming your table is sorted by salesperson as is shown in your sample.

Assume the table is in the range G1:H5.

As the source for the drop down in B1 use this formula:

=OFFSET(H1,MATCH(A1,G1:G5,0)-1,,COUNTIF(G1:G5,A1))

If A1 is empty when you set this up you'll get a message that says
something
like: the source currently evaluates to an error...do you want to
continue?
Just answer YES to this message.

Something to consider...

If you select Mike and Balls then you change it and select Frank, Balls
is
still selected in B1 and this is not a valid selection for Frank.


--
Biff
Microsoft Excel MVP


"MichaelR" wrote in message
...
Hi,

I have two cells with dropdowns in them - cells A2 and B2. In A2 I used
the
data validation to only allow my salespeople to be selected and now I
want
to
make a validation in cell B2 that will only allow the products that my
salesperson is allowed to sell.

I have a table that lists all of the salespeople multiple times in one
column and then the product that they can sell listed in the next
column
(each on its own row). For example:

Mike Balls
Mike Racquets
Mike Strings
Frank Strings
Frank Wristbands
etc.

Is it possible for the data validation in cell B1 to look into cell A1
and
then look into the table to find which products the salesperson is
allowed?

Any help is greatly appreciated.

Thanks,
Michael