Data validation in Excel 2007

  • Thread starter Thread starter Aeronav
  • Start date Start date
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.
 
You may use "=Tableau1[[#données];[Produit]]" to define a dynamic
name...
Regards.
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...
 
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...
 
Merci de votre mise au point, j'étais passé à côté de la plaque. Ça marche
effectivement.
Bern.
 
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.
 
Back
Top