unique key in an excel 2007 table

V

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
 
R

Roger Govier

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

--

Regards
Roger Govier

vsoler said:
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
 
V

vsoler

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.

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?
 
R

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

vsoler said:
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.

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
 
V

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.

__________ 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
 
R

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

vsoler said:
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.

__________ 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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top