Data Validation dependant and unique
 

Hi all,
I need to create a data validation drop down that checks for uniqueness over
the list it's part of and dependant on content of another list... here's what
I mean:

ColA ColB
ABC 123
ABC 234
ABC <validation checks that the entry belongs to ABC and is not 123 or 234
DFE <validation allows entry of 123 because DFE doesn't have it

Now, I can produce a drop down list dependant on another column thanks to
http://www.contextures.com/xlDataVal13.html :

OFFSET(ColAStart,MATCH(B3,AllColA,0)-1,1,COUNTIF(AllColA,B3),1)

I can also check for uniqueness formula with this formula:

ISNA(VLOOKUP(B3,B$2:B2,1,FALSE))

Both formulas are working well in other places. My thought is that I can
combine them with the Offset formula as the VLookup formula's lookup range,
so:

ISNA(VLOOKUP(B3,<that offset function,1,FALSE))

However, nothing happens... no error, no list...?

If I copy that formula out to another blank sheet to break it down and test
it I get a circular reference error.

I can't have the ColB entries elsewhere as this is where the ColB list is to
be created. Can anyone help?

TIA, Sara

If you don't want to reuse items from the existing list, why do you want
to create a dropdown of previously entered items?

Or is there another list in your workbook that contains the ColAStart
and AllColA ranges?

Sara Hopkins wrote:
Hi all,
I need to create a data validation drop down that checks for uniqueness over
the list it's part of and dependant on content of another list... here's what
I mean:

ColA ColB
ABC 123
ABC 234
ABC <validation checks that the entry belongs to ABC and is not 123 or 234
DFE <validation allows entry of 123 because DFE doesn't have it

Now, I can produce a drop down list dependant on another column thanks to
http://www.contextures.com/xlDataVal13.html :

OFFSET(ColAStart,MATCH(B3,AllColA,0)-1,1,COUNTIF(AllColA,B3),1)

I can also check for uniqueness formula with this formula:

ISNA(VLOOKUP(B3,B$2:B2,1,FALSE))

Both formulas are working well in other places. My thought is that I can
combine them with the Offset formula as the VLookup formula's lookup range,
so:

ISNA(VLOOKUP(B3,<that offset function,1,FALSE))

However, nothing happens... no error, no list...?

If I copy that formula out to another blank sheet to break it down and test
it I get a circular reference error.

I can't have the ColB entries elsewhere as this is where the ColB list is to
be created. Can anyone help?

TIA, Sara


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thanks Debra, I don't actually want a drop down at all, I want the data
validation to stop a user from entering duplicate ColB values over ColA
entries, so unique over ColA values but not unique over all. ColA is itself
a data validation lookup to another spreadsheet in the workbook.

The purpose of the Workbook is data collection of Customers (ColA) and their
ShipTos (ColB) the particular spreadsheet this data comes from is the ShipTo
collection.

"Debra Dalgleish" wrote:

If you don't want to reuse items from the existing list, why do you want
to create a dropdown of previously entered items?

Or is there another list in your workbook that contains the ColAStart
and AllColA ranges?

Sara Hopkins wrote:
Hi all,
I need to create a data validation drop down that checks for uniqueness over
the list it's part of and dependant on content of another list... here's what
I mean:

ColA ColB
ABC 123
ABC 234
ABC <validation checks that the entry belongs to ABC and is not 123 or 234
DFE <validation allows entry of 123 because DFE doesn't have it

Now, I can produce a drop down list dependant on another column thanks to
http://www.contextures.com/xlDataVal13.html :

OFFSET(ColAStart,MATCH(B3,AllColA,0)-1,1,COUNTIF(AllColA,B3),1)

I can also check for uniqueness formula with this formula:

ISNA(VLOOKUP(B3,B$2:B2,1,FALSE))

Both formulas are working well in other places. My thought is that I can
combine them with the Offset formula as the VLookup formula's lookup range,
so:

ISNA(VLOOKUP(B3,<that offset function,1,FALSE))

However, nothing happens... no error, no list...?

If I copy that formula out to another blank sheet to break it down and test
it I get a circular reference error.

I can't have the ColB entries elsewhere as this is where the ColB list is to
be created. Can anyone help?

TIA, Sara


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

To check for unique combinations, you could use a SumProduct formula, e.g.:

=SUMPRODUCT(--($A$2:$A$200=A2),--($B$2:$B$200=B2))<2

Sara Hopkins wrote:
Thanks Debra, I don't actually want a drop down at all, I want the data
validation to stop a user from entering duplicate ColB values over ColA
entries, so unique over ColA values but not unique over all. ColA is itself
a data validation lookup to another spreadsheet in the workbook.

The purpose of the Workbook is data collection of Customers (ColA) and their
ShipTos (ColB) the particular spreadsheet this data comes from is the ShipTo
collection.

"Debra Dalgleish" wrote:


If you don't want to reuse items from the existing list, why do you want
to create a dropdown of previously entered items?

Or is there another list in your workbook that contains the ColAStart
and AllColA ranges?

Sara Hopkins wrote:

Hi all,
I need to create a data validation drop down that checks for uniqueness over
the list it's part of and dependant on content of another list... here's what
I mean:

ColA ColB
ABC 123
ABC 234
ABC <validation checks that the entry belongs to ABC and is not 123 or 234
DFE <validation allows entry of 123 because DFE doesn't have it

Now, I can produce a drop down list dependant on another column thanks to
http://www.contextures.com/xlDataVal13.html :

OFFSET(ColAStart,MATCH(B3,AllColA,0)-1,1,COUNTIF(AllColA,B3),1)

I can also check for uniqueness formula with this formula:

ISNA(VLOOKUP(B3,B$2:B2,1,FALSE))

Both formulas are working well in other places. My thought is that I can
combine them with the Offset formula as the VLookup formula's lookup range,
so:

ISNA(VLOOKUP(B3,<that offset function,1,FALSE))

However, nothing happens... no error, no list...?

If I copy that formula out to another blank sheet to break it down and test
it I get a circular reference error.

I can't have the ColB entries elsewhere as this is where the ColB list is to
be created. Can anyone help?

TIA, Sara


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Thanks Debra, that worked. I wonder, what the significance of the double
dashes is? I initially took them out only to have the formula fail. When I
put them back in again the formula worked.

"Debra Dalgleish" wrote:

To check for unique combinations, you could use a SumProduct formula, e.g.:

=SUMPRODUCT(--($A$2:$A$200=A2),--($B$2:$B$200=B2))<2

Sara Hopkins wrote:
Thanks Debra, I don't actually want a drop down at all, I want the data
validation to stop a user from entering duplicate ColB values over ColA
entries, so unique over ColA values but not unique over all. ColA is itself
a data validation lookup to another spreadsheet in the workbook.

The purpose of the Workbook is data collection of Customers (ColA) and their
ShipTos (ColB) the particular spreadsheet this data comes from is the ShipTo
collection.

"Debra Dalgleish" wrote:


If you don't want to reuse items from the existing list, why do you want
to create a dropdown of previously entered items?

Or is there another list in your workbook that contains the ColAStart
and AllColA ranges?

Sara Hopkins wrote:

Hi all,
I need to create a data validation drop down that checks for uniqueness over
the list it's part of and dependant on content of another list... here's what
I mean:

ColA ColB
ABC 123
ABC 234
ABC <validation checks that the entry belongs to ABC and is not 123 or 234
DFE <validation allows entry of 123 because DFE doesn't have it

Now, I can produce a drop down list dependant on another column thanks to
http://www.contextures.com/xlDataVal13.html :

OFFSET(ColAStart,MATCH(B3,AllColA,0)-1,1,COUNTIF(AllColA,B3),1)

I can also check for uniqueness formula with this formula:

ISNA(VLOOKUP(B3,B$2:B2,1,FALSE))

Both formulas are working well in other places. My thought is that I can
combine them with the Offset formula as the VLookup formula's lookup range,
so:

ISNA(VLOOKUP(B3,<that offset function,1,FALSE))

However, nothing happens... no error, no list...?

If I copy that formula out to another blank sheet to break it down and test
it I get a circular reference error.

I can't have the ColB entries elsewhere as this is where the ColB list is to
be created. Can anyone help?

TIA, Sara


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

J.E. McGimpsey has an explanation he

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

Sara Hopkins wrote:
Thanks Debra, that worked. I wonder, what the significance of the double
dashes is? I initially took them out only to have the formula fail. When I
put them back in again the formula worked.

"Debra Dalgleish" wrote:


To check for unique combinations, you could use a SumProduct formula, e.g.:

=SUMPRODUCT(--($A$2:$A$200=A2),--($B$2:$B$200=B2))<2

Sara Hopkins wrote:

Thanks Debra, I don't actually want a drop down at all, I want the data
validation to stop a user from entering duplicate ColB values over ColA
entries, so unique over ColA values but not unique over all. ColA is itself
a data validation lookup to another spreadsheet in the workbook.

The purpose of the Workbook is data collection of Customers (ColA) and their
ShipTos (ColB) the particular spreadsheet this data comes from is the ShipTo
collection.

"Debra Dalgleish" wrote:



If you don't want to reuse items from the existing list, why do you want
to create a dropdown of previously entered items?

Or is there another list in your workbook that contains the ColAStart
and AllColA ranges?

Sara Hopkins wrote:


Hi all,
I need to create a data validation drop down that checks for uniqueness over
the list it's part of and dependant on content of another list... here's what
I mean:

ColA ColB
ABC 123
ABC 234
ABC <validation checks that the entry belongs to ABC and is not 123 or 234
DFE <validation allows entry of 123 because DFE doesn't have it

Now, I can produce a drop down list dependant on another column thanks to
http://www.contextures.com/xlDataVal13.html :

OFFSET(ColAStart,MATCH(B3,AllColA,0)-1,1,COUNTIF(AllColA,B3),1)

I can also check for uniqueness formula with this formula:

ISNA(VLOOKUP(B3,B$2:B2,1,FALSE))

Both formulas are working well in other places. My thought is that I can
combine them with the Offset formula as the VLookup formula's lookup range,
so:

ISNA(VLOOKUP(B3,<that offset function,1,FALSE))

However, nothing happens... no error, no list...?

If I copy that formula out to another blank sheet to break it down and test
it I get a circular reference error.

I can't have the ColB entries elsewhere as this is where the ColB list is to
be created. Can anyone help?

TIA, Sara


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html