Hi All, I have an spreadsheet with a drop down list ( I folow Debra guide to
create it), in total 4 columns

Region Sales Rep Business Markets/Customer
GNA cda Applications Electricity

Once selected the region in the Sales rep column it will bring all the sales
rep for that region, however if I select a region and sales rep and then come
back and change the region and skip to change the sales rep, there is nothing
that will stop me for doing it. I thought to enter a column that will lookup
the sales rep into the GNA region and if then it doesn't match with Sales rep
it will Highlight in red. I named the column for the regional sales rep in
this case "GNAColumn". I am trying the formula
=+VLOOKUP(E5,D5&"Column",1,FALSE), but it's bringing #VALUE.
Can somebody help me with this then I will be able to apply the same logic
to the other 2 Columns. Thank you

Let's assume you have 2 named lists, Region, and Rep. If I understand you,
you are concerned because someone can choose a region, then a rep, then
change the region. A simple way of preventing that is to add a little more DV
to the Region area.
Assume that Region drop down is in A2, and Rep drop down is in B2. For the
DV for Region, you would still have LIST for Allow: and instead of having
Region for Source: you would instead of something like the following:
=INDIRECT("Region"&IF(B2="","",1))
Basically, if anything is in the cell that represents the Rep list, this
will change the list for region from Region, to Region1, which, of course,
isn't defined, and therefore no pull down menu is available. The user would
have to have an empty cell in B2 for the regular drop down list to appear for
A2. Of course, as always, anyone can copy/paste into a DV no matter the
contents, but that is how it is now anyways.

Hope this helps.
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Eduardo" wrote:

Hi All, I have an spreadsheet with a drop down list ( I folow Debra guide to
create it), in total 4 columns

Region Sales Rep Business Markets/Customer
GNA cda Applications Electricity

Once selected the region in the Sales rep column it will bring all the sales
rep for that region, however if I select a region and sales rep and then come
back and change the region and skip to change the sales rep, there is nothing
that will stop me for doing it. I thought to enter a column that will lookup
the sales rep into the GNA region and if then it doesn't match with Sales rep
it will Highlight in red. I named the column for the regional sales rep in
this case "GNAColumn". I am trying the formula
=+VLOOKUP(E5,D5&"Column",1,FALSE), but it's bringing #VALUE.
Can somebody help me with this then I will be able to apply the same logic
to the other 2 Columns. Thank you

Thank you John for answering, I think I need to be more clear

This is what I have
A list called Region1 whith the different region
A list called XXXColumn. XXX represent each region (I have for different
ones) where I have the sales rep.
In order to pickup the sales rep I have in the DV the formula as follow

=OFFSET(INDIRECT(B5),0,0,COUNTA(INDIRECT(B5&"Colum n")),1)
In my calculations I have the columns as follow
Product Region SalesRep

Your assumptions were correct, but if I modify your code from B2 to C5 it
works, but then it brings the product list instead of the salesrep list

I appreciatte your help, thank you

"John C" wrote:

Let's assume you have 2 named lists, Region, and Rep. If I understand you,
you are concerned because someone can choose a region, then a rep, then
change the region. A simple way of preventing that is to add a little more DV
to the Region area.
Assume that Region drop down is in A2, and Rep drop down is in B2. For the
DV for Region, you would still have LIST for Allow: and instead of having
Region for Source: you would instead of something like the following:
=INDIRECT("Region"&IF(B2="","",1))
Basically, if anything is in the cell that represents the Rep list, this
will change the list for region from Region, to Region1, which, of course,
isn't defined, and therefore no pull down menu is available. The user would
have to have an empty cell in B2 for the regular drop down list to appear for
A2. Of course, as always, anyone can copy/paste into a DV no matter the
contents, but that is how it is now anyways.

Hope this helps.
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Eduardo" wrote:

Hi All, I have an spreadsheet with a drop down list ( I folow Debra guide to
create it), in total 4 columns

Region Sales Rep Business Markets/Customer
GNA cda Applications Electricity

Once selected the region in the Sales rep column it will bring all the sales
rep for that region, however if I select a region and sales rep and then come
back and change the region and skip to change the sales rep, there is nothing
that will stop me for doing it. I thought to enter a column that will lookup
the sales rep into the GNA region and if then it doesn't match with Sales rep
it will Highlight in red. I named the column for the regional sales rep in
this case "GNAColumn". I am trying the formula
=+VLOOKUP(E5,D5&"Column",1,FALSE), but it's bringing #VALUE.
Can somebody help me with this then I will be able to apply the same logic
to the other 2 Columns. Thank you

Well, didn't know you actually had a Region1 list. My point was to add
something, anything, to your Region list that would make it an invalid list.
What happens in DV if you have an invalid list, is, when you click on the
down arrow, absolutely nothing happens. No drop down, no nothing.

You could modify my formula as follows:
=INDIRECT("Region"&IF(B2="","","nolist"))
--
** John C **

"Eduardo" wrote:

Thank you John for answering, I think I need to be more clear

This is what I have
A list called Region1 whith the different region
A list called XXXColumn. XXX represent each region (I have for different
ones) where I have the sales rep.
In order to pickup the sales rep I have in the DV the formula as follow

=OFFSET(INDIRECT(B5),0,0,COUNTA(INDIRECT(B5&"Colum n")),1)
In my calculations I have the columns as follow
Product Region SalesRep

Your assumptions were correct, but if I modify your code from B2 to C5 it
works, but then it brings the product list instead of the salesrep list

I appreciatte your help, thank you

"John C" wrote:

Let's assume you have 2 named lists, Region, and Rep. If I understand you,
you are concerned because someone can choose a region, then a rep, then
change the region. A simple way of preventing that is to add a little more DV
to the Region area.
Assume that Region drop down is in A2, and Rep drop down is in B2. For the
DV for Region, you would still have LIST for Allow: and instead of having
Region for Source: you would instead of something like the following:
=INDIRECT("Region"&IF(B2="","",1))
Basically, if anything is in the cell that represents the Rep list, this
will change the list for region from Region, to Region1, which, of course,
isn't defined, and therefore no pull down menu is available. The user would
have to have an empty cell in B2 for the regular drop down list to appear for
A2. Of course, as always, anyone can copy/paste into a DV no matter the
contents, but that is how it is now anyways.

Hope this helps.
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Eduardo" wrote:

Hi All, I have an spreadsheet with a drop down list ( I folow Debra guide to
create it), in total 4 columns

Region Sales Rep Business Markets/Customer
GNA cda Applications Electricity

Once selected the region in the Sales rep column it will bring all the sales
rep for that region, however if I select a region and sales rep and then come
back and change the region and skip to change the sales rep, there is nothing
that will stop me for doing it. I thought to enter a column that will lookup
the sales rep into the GNA region and if then it doesn't match with Sales rep
it will Highlight in red. I named the column for the regional sales rep in
this case "GNAColumn". I am trying the formula
=+VLOOKUP(E5,D5&"Column",1,FALSE), but it's bringing #VALUE.
Can somebody help me with this then I will be able to apply the same logic
to the other 2 Columns. Thank you

THANK YOU John, that works exactly what I need to do. Thank you again

"John C" wrote:

Well, didn't know you actually had a Region1 list. My point was to add
something, anything, to your Region list that would make it an invalid list.
What happens in DV if you have an invalid list, is, when you click on the
down arrow, absolutely nothing happens. No drop down, no nothing.

You could modify my formula as follows:
=INDIRECT("Region"&IF(B2="","","nolist"))
--
** John C **

"Eduardo" wrote:

Thank you John for answering, I think I need to be more clear

This is what I have
A list called Region1 whith the different region
A list called XXXColumn. XXX represent each region (I have for different
ones) where I have the sales rep.
In order to pickup the sales rep I have in the DV the formula as follow

=OFFSET(INDIRECT(B5),0,0,COUNTA(INDIRECT(B5&"Colum n")),1)
In my calculations I have the columns as follow
Product Region SalesRep

Your assumptions were correct, but if I modify your code from B2 to C5 it
works, but then it brings the product list instead of the salesrep list

I appreciatte your help, thank you

"John C" wrote:

Let's assume you have 2 named lists, Region, and Rep. If I understand you,
you are concerned because someone can choose a region, then a rep, then
change the region. A simple way of preventing that is to add a little more DV
to the Region area.
Assume that Region drop down is in A2, and Rep drop down is in B2. For the
DV for Region, you would still have LIST for Allow: and instead of having
Region for Source: you would instead of something like the following:
=INDIRECT("Region"&IF(B2="","",1))
Basically, if anything is in the cell that represents the Rep list, this
will change the list for region from Region, to Region1, which, of course,
isn't defined, and therefore no pull down menu is available. The user would
have to have an empty cell in B2 for the regular drop down list to appear for
A2. Of course, as always, anyone can copy/paste into a DV no matter the
contents, but that is how it is now anyways.

Hope this helps.
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Eduardo" wrote:

Hi All, I have an spreadsheet with a drop down list ( I folow Debra guide to
create it), in total 4 columns

Region Sales Rep Business Markets/Customer
GNA cda Applications Electricity

Once selected the region in the Sales rep column it will bring all the sales
rep for that region, however if I select a region and sales rep and then come
back and change the region and skip to change the sales rep, there is nothing
that will stop me for doing it. I thought to enter a column that will lookup
the sales rep into the GNA region and if then it doesn't match with Sales rep
it will Highlight in red. I named the column for the regional sales rep in
this case "GNAColumn". I am trying the formula
=+VLOOKUP(E5,D5&"Column",1,FALSE), but it's bringing #VALUE.
Can somebody help me with this then I will be able to apply the same logic
to the other 2 Columns. Thank you

Glad to help, and thanks for the feedback :)
--
** John C **

"Eduardo" wrote:

THANK YOU John, that works exactly what I need to do. Thank you again

"John C" wrote:

Well, didn't know you actually had a Region1 list. My point was to add
something, anything, to your Region list that would make it an invalid list.
What happens in DV if you have an invalid list, is, when you click on the
down arrow, absolutely nothing happens. No drop down, no nothing.

You could modify my formula as follows:
=INDIRECT("Region"&IF(B2="","","nolist"))
--
** John C **

"Eduardo" wrote:

Thank you John for answering, I think I need to be more clear

This is what I have
A list called Region1 whith the different region
A list called XXXColumn. XXX represent each region (I have for different
ones) where I have the sales rep.
In order to pickup the sales rep I have in the DV the formula as follow

=OFFSET(INDIRECT(B5),0,0,COUNTA(INDIRECT(B5&"Colum n")),1)
In my calculations I have the columns as follow
Product Region SalesRep

Your assumptions were correct, but if I modify your code from B2 to C5 it
works, but then it brings the product list instead of the salesrep list

I appreciatte your help, thank you

"John C" wrote:

Let's assume you have 2 named lists, Region, and Rep. If I understand you,
you are concerned because someone can choose a region, then a rep, then
change the region. A simple way of preventing that is to add a little more DV
to the Region area.
Assume that Region drop down is in A2, and Rep drop down is in B2. For the
DV for Region, you would still have LIST for Allow: and instead of having
Region for Source: you would instead of something like the following:
=INDIRECT("Region"&IF(B2="","",1))
Basically, if anything is in the cell that represents the Rep list, this
will change the list for region from Region, to Region1, which, of course,
isn't defined, and therefore no pull down menu is available. The user would
have to have an empty cell in B2 for the regular drop down list to appear for
A2. Of course, as always, anyone can copy/paste into a DV no matter the
contents, but that is how it is now anyways.

Hope this helps.
--
** John C **
Please remember if your question is answered, to mark it answered :). It
helps everyone.


"Eduardo" wrote:

Hi All, I have an spreadsheet with a drop down list ( I folow Debra guide to
create it), in total 4 columns

Region Sales Rep Business Markets/Customer
GNA cda Applications Electricity

Once selected the region in the Sales rep column it will bring all the sales
rep for that region, however if I select a region and sales rep and then come
back and change the region and skip to change the sales rep, there is nothing
that will stop me for doing it. I thought to enter a column that will lookup
the sales rep into the GNA region and if then it doesn't match with Sales rep
it will Highlight in red. I named the column for the regional sales rep in
this case "GNAColumn". I am trying the formula
=+VLOOKUP(E5,D5&"Column",1,FALSE), but it's bringing #VALUE.
Can somebody help me with this then I will be able to apply the same logic
to the other 2 Columns. Thank you