I would like to include drop down lists on an Order Entry form. To do this I'm using DATA VALIDATION and the INDIRECT functions.

The application includes several Data Validation drop down lists. Two of these drop down lists are problematic, the rest work well.

The two problematic cells use the same SOURCE definition, a "product name", but each of the two cells is to present a different selection on the drop down list.

One of the drop down lists should present the "SIZE" of the selected product, the second drop down list should present the "WEIGHT". Both of these drop downs use the same SOURCE selection, i.e. "PRODUCT NAME".

Each Data Validation table is defined on its own worksheet using the same SOURCE reference and both using the INDIRECT function. I used the FORMULAS > CREATE FROM SELECTIONS command to define the table. The worksheet names, SIZE and WEIGHT, respectively.

When I defined the SIZE worksheet, the drop down list presented was correct and worked well.

I then defined the WEIGHT worksheet the same way and the drop down for the weights was correct and worked well.

The problem I have surfaced after defining the WEIGHT worksheet.

After defining the WEIGHT worksheet table and testing the SIZE worksheet validation again, the SIZE drop down list selection had been replaced by the WEIGHT drop down list selection.



The SOURCE entry is the only thing these two worksheets have in common.

Suggestions on how to resolve this problem?

thanks - BullMouse