Excel data validation: dropdown list
Solved
Fadjerx
-
eriiic Posted messages 24581 Registration date Status Contributeur Last intervention -
eriiic Posted messages 24581 Registration date Status Contributeur Last intervention -
Hello friends,
I have wasted a lot of time on something in Excel that logically shouldn't be difficult to do.
In fact, for the values in a column of my file, I want the user to choose their values from a dropdown list only. I don't want them to enter an additional value.
So I used the trick of: Data > Validation after defining my list in Insert > Name > Define
Since the values in the defined list may change, I tried to create a dynamic list using the formula: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
My Excel has never accepted this formula! It tells me that there is an error, knowing that my values are indeed in the first column... I'm using Excel 2002.
Then I looked for a way to do this in VBA code. That is to say, define a normal static list and then, using a small macro run at the opening of the file, I update the values of this list. I can't seem to do it!
A help would really be appreciated :-(
Best regards.
I have wasted a lot of time on something in Excel that logically shouldn't be difficult to do.
In fact, for the values in a column of my file, I want the user to choose their values from a dropdown list only. I don't want them to enter an additional value.
So I used the trick of: Data > Validation after defining my list in Insert > Name > Define
Since the values in the defined list may change, I tried to create a dynamic list using the formula: =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
My Excel has never accepted this formula! It tells me that there is an error, knowing that my values are indeed in the first column... I'm using Excel 2002.
Then I looked for a way to do this in VBA code. That is to say, define a normal static list and then, using a small macro run at the opening of the file, I update the values of this list. I can't seem to do it!
A help would really be appreciated :-(
Best regards.
Configuration: Windows XP Firefox 2.0.0.9
9 réponses
Hello
If the list of possible values (ALLOW: LIST) is in another sheet of the same workbook or in another workbook, you need to define a name (DEFINE…/NAME/INSERT) that refers to that list in the other sheet or in the other workbook. In the SOURCE field, you reference the name listed on the same worksheet.
For example, if the list of valid entries is in cells A1:A10 of the first worksheet of a workbook named ValeursPossibles.xls, you should define the name VpCharge on the active worksheet (the one containing the validation) as
=[ValeursPossibles.xls]Sheet1!$A$1:$A$10, then enter =VpCharge in the SOURCE field.
If the list of possible values (ALLOW: LIST) is in another sheet of the same workbook or in another workbook, you need to define a name (DEFINE…/NAME/INSERT) that refers to that list in the other sheet or in the other workbook. In the SOURCE field, you reference the name listed on the same worksheet.
For example, if the list of valid entries is in cells A1:A10 of the first worksheet of a workbook named ValeursPossibles.xls, you should define the name VpCharge on the active worksheet (the one containing the validation) as
=[ValeursPossibles.xls]Sheet1!$A$1:$A$10, then enter =VpCharge in the SOURCE field.
