Data Validation dependant and unique

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

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

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

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

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