Dynamic data validation list is empty when spreadsheet is first opened

[phaedrus1313]

Hi everyone,

(Windows 7, Excel 2010)

I'm using the INDIRECT and MATCH formula functions to create a data validation drop-down list that is based on a variable-length list of data items. When I setup the data validation list, everything is fine. But when I save, close, and re-open the spreadsheet, the drop-down list is empty (i.e. nothing happens when I click on the arrow) until I go back into the Data Validation dialog box and click OK (without making any changes).

I've put a spreadsheet that demonstrates the problem at http://dl.dropbox.com/u/11365687/dynamic-validation-list.xlsx [9k]

Here is a description of how I can reproduce the problem:

• In a blank worksheet, enter some data values in column C, starting in C1
• Select cell A1, and go into the Data Validation dialog box.
• Choose "List", and in the Source field enter: =INDIRECT("$C$1:$C$" & MATCH(2, 1/(C:C<>""),1))
• Click OK
• You should now have a drop-down list in cell A1 with the choices being the contents of column C. If you add or remove a value from column C, the choices in A1 should update automatically.
• Save the spreadsheet, close it, and re-open it.
• Click on A1 and try to open the drop-down list. Nothing should happen when you click on the arrow. This is the problem I'm seeing.
• If you then select A1, go back into the Data Validation dialog box, you'll see that the formula is still there properly. Click OK without making any changes, and now the drop-down works in A1 again.

Any suggestions or pointers to information about this problem would be very much appreciated!

thanks,
phaedrus1313

 

[Aladin Akyurek]

Hi everyone,

(Windows 7, Excel 2010)

I'm using the INDIRECT and MATCH formula functions to create a data validation drop-down list that is based on a variable-length list of data items. When I setup the data validation list, everything is fine. But when I save, close, and re-open the spreadsheet, the drop-down list is empty (i.e. nothing happens when I click on the arrow) until I go back into the Data Validation dialog box and click OK (without making any changes).

I've put a spreadsheet that demonstrates the problem at http://dl.dropbox.com/u/11365687/dynamic-validation-list.xlsx [9k]

Here is a description of how I can reproduce the problem:

• In a blank worksheet, enter some data values in column C, starting in C1
• Select cell A1, and go into the Data Validation dialog box.
• Choose "List", and in the Source field enter: =INDIRECT("$C$1:$C$" & MATCH(2, 1/(C:C<>""),1))
• Click OK
• You should now have a drop-down list in cell A1 with the choices being the contents of column C. If you add or remove a value from column C, the choices in A1 should update automatically.
• Save the spreadsheet, close it, and re-open it.
• Click on A1 and try to open the drop-down list. Nothing should happen when you click on the arrow. This is the problem I'm seeing.
• If you then select A1, go back into the Data Validation dialog box, you'll see that the formula is still there properly. Click OK without making any changes, and now the drop-down works in A1 again.

Any suggestions or pointers to information about this problem would be very much appreciated!

thanks,
phaedrus1313

Try rather...

Define List by means of Formulas | Name Manager as referring to:

=Sheet1!$E$4:INDEX(Sheet1!$E:$E,MATCH(REPT("z",255),Sheet1!$E:$E))

Data validate C3 as...

Allow: List

Source:

=List

 

[phaedrus1313]

Brilliant! I still don't understand why the first way has the problem it does, but doing it with the named range like this works brilliantly.

thanks very much,
phaedrus1313

 

[Aladin Akyurek]

Brilliant! I still don't understand why the first way has the problem it does, but doing it with the named range like this works brilliantly.

thanks very much,
phaedrus1313

You are welcome. Thanks for providing feedback.

By the way...

Allow: List

Source:

=INDIRECT("Sheet1!$E$4:$E$"&MATCH(2,1/(Sheet1!$E:$E<>""),1))

should work, but this would lock the list to a particular location and it's a bit expensive.