Data validation in Excel 2007

A

Aeronav

Hey,
In Excel 2002 I could create a dynamic name
=DECALER(Feuil1!$A$1;;;NBVAL($A:$A)-1;1)
to cover a list of data used for setting validation rules.
In Excel 2007 I tried to use the new "structured references" for the same
purpose, but Excel refuses, saying that there is an error in my formula.
For example : =Tableau1[ [#données] ; [Produit] ] which works well for
formulas in cells ( french version needs a semi-colon for separator) is not
accepted in the validation dialog box.
Is it impossible ? and I will stay with dynamic names, or is there a way to
turn around this ?
Thanks
Bern.
 
D

Daniel.C

You may use "=Tableau1[[#données];[Produit]]" to define a dynamic
name...
Regards.
Daniel
 
A

Aeronav

Sorry, the formula which you submit is exactly the one I tried to use in the
validation dialog box as source list.
I tried once more to use it there and I always get a formula error message.
When the message is dismissed the entire formula is selected.
The formula is accepted when used in a spreadsheet cell after selecting a
number of cells at least equal to the number of products, and Array entered
(CTRL+MAJ+ENTER).
Regards
Bern.

Daniel.C said:
You may use "=Tableau1[[#données];[Produit]]" to define a dynamic name...
Regards.
Daniel
Hey,
In Excel 2002 I could create a dynamic name
=DECALER(Feuil1!$A$1;;;NBVAL($A:$A)-1;1)
to cover a list of data used for setting validation rules.
In Excel 2007 I tried to use the new "structured references" for the same
purpose, but Excel refuses, saying that there is an error in my formula.
For example : =Tableau1[ [#données] ; [Produit] ] which works well for
formulas in cells ( french version needs a semi-colon for separator) is
not accepted in the validation dialog box.
Is it impossible ? and I will stay with dynamic names, or is there a way
to turn around this ?
Thanks
Bern.
Click to expand...
Click to expand...
 
D

Daniel.C

What i want to say is that you have to use that formula to define a
dynamic named range. Then use the named range for the validation list.
Daniel
Sorry, the formula which you submit is exactly the one I tried to use in the
validation dialog box as source list.
I tried once more to use it there and I always get a formula error message.
When the message is dismissed the entire formula is selected.
The formula is accepted when used in a spreadsheet cell after selecting a
number of cells at least equal to the number of products, and Array entered
(CTRL+MAJ+ENTER).
Regards
Bern.

Daniel.C said:
You may use "=Tableau1[[#données];[Produit]]" to define a dynamic name...
Regards.
Daniel
Hey,
In Excel 2002 I could create a dynamic name
=DECALER(Feuil1!$A$1;;;NBVAL($A:$A)-1;1)
to cover a list of data used for setting validation rules.
In Excel 2007 I tried to use the new "structured references" for the same
purpose, but Excel refuses, saying that there is an error in my formula.
For example : =Tableau1[ [#données] ; [Produit] ] which works well for
formulas in cells ( french version needs a semi-colon for separator) is
not accepted in the validation dialog box.
Is it impossible ? and I will stay with dynamic names, or is there a way
to turn around this ?
Thanks
Bern.
Click to expand...
Click to expand...
Click to expand...
 
A

Aeronav

Merci de votre mise au point, j'étais passé à côté de la plaque. Ça marche
effectivement.
Bern.
 
A

Aeronav

Je re-expédie le message rédigé le 11/11 et non apparu :
Merci de votre mise au point, j'étais passé à côté de la plaque. Ça marche
effectivement.
Bern.
 

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

Similar Threads

Data Validation with dynamic ranges 2
Using Data Validation in a Table 0
Excel 2007-macro programming logic for data validation 2
2007 data validation not working 1
how to write in a dropdown data(Validation) 2
how to change colon to semi-colon in CP/List Seprator 3
How To Use Range Names In Data Validation Criteria (Not For Lists) 1
EXCEL 2007 - DATA VALIDATION LIST 2

Top