Scenario: Create dropdown picker list from set of rows in another sheet. Then, attempt to use this dropdown list in multiple rows on another sheet.

Problem: Using a dropdown list created with Data Validity works fine for exactly one (1) cell in another sheet. However, it's not useful if you have more than one row that needs to use this same dropdown list because neither "copy and paste" nor does "drag copy" work properly with the Data Validity "Source" when using a Lookup from another sheet's values.

This problem means manually creating Data Validity separately for each cell, one at a time, which is very time consuming. Attempting to time save using copy / paste or drag paste is impossible with this bug.

Steps:

1) Create a sheet named "sheet" with multiple rows
2) Create a second sheet with three rows of values (to be used as dropdown pick list). I named sheet "Lookup".
3) In "sheet", select a cell and use Data Validity with 'Cell Range'
4) In the Source field, enter 'Lookup.A1:A10' (I'm using more than 3 rows here for easy expansion of the picker list)
5) Return to "sheet" and validate the correct dropdown list for that cell is now present.
6) Drag copy that cell downward by one. In Data Validity Source, for that copied cell you'll now notice that the paste process has shifted the Source field values by 1 for each copy. The Source now looks like "Lookup.A2:A11", which is incorrect. Why would we want this?
7) Drag copying downward again makes Source look like "Lookup.A3:A12". Again, why would you want this?
8) Attempting copy/paste forces this same "Source" increment problem.

When drag copying, I've attempted to press and hold the 'Command' key qualifier, which prevents incrementing values when copying cell-to-cell. While OpenOffice respects this qualifier key press for cell-to-cell copying, it ignores this qualifier key when pasting Data Validity dropdown Source information. In fact, I've found no way to copy paste a cell using a Data Validity dropdown and retain the required "Lookup.A1:A10" value.

I'm not even sure of the reasoning behind incrementing values in the Source field when using Data Validity.

Expected Outcome: Source information in Data Validity for 'Cell List' should not increment by default. If incrementing is required, there should be a checkbox in Data Validity to allow for incrementing on drag copy and/or copy&paste.