unique key in an excel 2007 table

[vsoler]

Hello,

In an excel 2007 table, I want that one column has unique values. I
want to check this at input time, with a data validation function.

Say that I have Table1

a b
qw 1
as 2
zx 3
fg 4

where a and b are column names. I don't want another "qw" or "zx"
value in column a.

I have tried with Data/Data Validation, with a formula like

=(countif([a];[a])=1)

but the countif function does not seem to recognaize structured
references in data validation formulas.

How could I get around this problem?

Thank you

 

[Roger Govier]

Hi
Try
=COUNTIF($A:$A;A1)=1

--

Regards
Roger Govier

Hello,

In an excel 2007 table, I want that one column has unique values. I
want to check this at input time, with a data validation function.

Say that I have Table1

a b
qw 1
as 2
zx 3
fg 4

where a and b are column names. I don't want another "qw" or "zx"
value in column a.

I have tried with Data/Data Validation, with a formula like

=(countif([a];[a])=1)

but the countif function does not seem to recognaize structured
references in data validation formulas.

How could I get around this problem?

Thank you

__________ Information from ESET Smart Security, version of virus
signature database 5226 (20100624) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 5226 (20100624) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[vsoler]

Hi
Try
=COUNTIF($A:$A;A1)=1

--

Regards
Roger Govier

Hello,

In an excel 2007 table, I want that one column has unique values. I
want to check this at input time, with a data validation function.

Say that I have Table1

a b
qw 1
as 2
zx 3
fg 4

where a and b are column names. I don't want another "qw" or "zx"
value in column a.

I have tried with Data/Data Validation, with a formula like

=(countif([a];[a])=1)

but the countif function does not seem to recognaize structured
references in data validation formulas.

How could I get around this problem?

Thank you

__________ Information from ESET Smart Security, version of virus
signature database 5226 (20100624) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 5226 (20100624) __________

The message was checked by ESET Smart Security.

http://www.eset.com

I have some other data above and below my table, I cannot use $A:$A.

The formula should work on an expanding set of rows, that takes into
account all rows of Table1

Is this possible?

 

[Roger Govier]

Hi

Apologies, I skipped over the part where you mentioned it was a table.
try

=COUNTIF(Table1[name],Table1[[#This Row],[name]])

You may need to change the , to a ; for your Regional Settings

--

Regards
Roger Govier

Hi
Try
=COUNTIF($A:$A;A1)=1

--

Regards
Roger Govier

Hello,

In an excel 2007 table, I want that one column has unique values. I
want to check this at input time, with a data validation function.

Say that I have Table1

a b
qw 1
as 2
zx 3
fg 4

where a and b are column names. I don't want another "qw" or "zx"
value in column a.

I have tried with Data/Data Validation, with a formula like

=(countif([a];[a])=1)

but the countif function does not seem to recognaize structured
references in data validation formulas.

How could I get around this problem?

Thank you

__________ Information from ESET Smart Security, version of virus
signature database 5226 (20100624) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus
signature database 5226 (20100624) __________

The message was checked by ESET Smart Security.

http://www.eset.com

I have some other data above and below my table, I cannot use $A:$A.

The formula should work on an expanding set of rows, that takes into
account all rows of Table1

Is this possible?

__________ Information from ESET Smart Security, version of virus
signature database 5228 (20100625) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 5228 (20100625) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[vsoler]

Hi

Apologies, I skipped over the part where you mentioned it was a table.
try

=COUNTIF(Table1[name],Table1[[#This Row],[name]])

You may need to change the , to a ; for your Regional Settings

--

Regards
Roger Govier

Hi
Try
=COUNTIF($A:$A;A1)=1
--
Regards
Roger Govier

Hello,
In an excel 2007 table, I want that one column has unique values. I
want to check this at input time, with a data validation function.
Say that I have Table1
a b
qw 1
as 2
zx 3
fg 4
where a and b are column names. I don't want another "qw" or "zx"
value in column a.
I have tried with Data/Data Validation, with a formula like
=(countif([a];[a])=1)
but the countif function does not seem to recognaize structured
references in data validation formulas.
How could I get around this problem?
Thank you
__________ Information from ESET Smart Security, version of virus
signature database 5226 (20100624) __________
The message was checked by ESET Smart Security.
http://www.eset.com
__________ Information from ESET Smart Security, version of virus
signature database 5226 (20100624) __________
The message was checked by ESET Smart Security.
http://www.eset.com

I have some other data above and below my table, I cannot use $A:$A.

The formula should work on an expanding set of rows, that takes into
account all rows of Table1

Is this possible?

__________ Information from ESET Smart Security, version of virus
signature database 5228 (20100625) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 5228 (20100625) __________

The message was checked by ESET Smart Security.

http://www.eset.com

Hello Robert,

My excel model (with excel 2007) does not accept your formula. There
seems to be a problem with structured references

 

[Roger Govier]

Hi

Send me a copy of your workbook
To mail direct
roger at technology4u dot co dot uk
Change the at and dots and remove the spaces to make a valid email address.

--

Regards
Roger Govier

Hi

Apologies, I skipped over the part where you mentioned it was a table.
try

=COUNTIF(Table1[name],Table1[[#This Row],[name]])

You may need to change the , to a ; for your Regional Settings

--

Regards
Roger Govier

On 25 jun, 00:26, "Roger Govier" <[email protected]>
wrote:
Hi
Try
=COUNTIF($A:$A;A1)=1

Regards
Roger Govier

In an excel 2007 table, I want that one column has unique values. I
want to check this at input time, with a data validation function.

Say that I have Table1

a b
qw 1
as 2
zx 3
fg 4

where a and b are column names. I don't want another "qw" or "zx"
value in column a.

I have tried with Data/Data Validation, with a formula like

=(countif([a];[a])=1)

but the countif function does not seem to recognaize structured
references in data validation formulas.

How could I get around this problem?

Thank you

__________ Information from ESET Smart Security, version of virus
signature database 5226 (20100624) __________

The message was checked by ESET Smart Security.

__________ Information from ESET Smart Security, version of virus
signature database 5226 (20100624) __________

The message was checked by ESET Smart Security.

I have some other data above and below my table, I cannot use $A:$A.

The formula should work on an expanding set of rows, that takes into
account all rows of Table1

Is this possible?

__________ Information from ESET Smart Security, version of virus
signature database 5228 (20100625) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus
signature database 5228 (20100625) __________

The message was checked by ESET Smart Security.

http://www.eset.com

Hello Robert,

My excel model (with excel 2007) does not accept your formula. There
seems to be a problem with structured references

__________ Information from ESET Smart Security, version of virus
signature database 5229 (20100625) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 5229 (20100625) __________

The message was checked by ESET Smart Security.

http://www.eset.com