I'm trying to make a drop-down list in my spreadsheet, my spreadsheet has a couple of sheets but the main two sheets are "Master" and "Sheet3".

I have made the drop-down list using the Data -> Data validation as shown in the below screenshot:

And in the "Sheet3" sheet there is a big table (1000 columns) of data that I want to make a drop-down list from, from each column in it.

Now what I want is to drag copy this cell down so that the criteria will be like this:

1. =Sheet3!A1:A
2. =Sheet3!B1:B
3. =Sheet3!C1:C
4. =Sheet3!D1:D
5. ...etc

and so forth for 1000 rows, but when I do this, it does not increment the column letter, it's just like the first cell criteria value (=Sheet3!A1:A) for all other rows.

Is there a way to make this work without me having to edit each row in this 1000-row column manually?

Thank you all.

CodePudding user response：

• We have Sheet3 with 1000 column and we want to get a drop down of all 1000 column in Master sheet in a single column.

Solution

01 - Transpose Sheet3 to use it as a source of Data validation.

=TRANSPOSE(Sheet3!A1:999)

02 - Go to Data validation and set the range to ='Sheet3 Transposed'!1:1 as soon as you click save it automatically changes to ='Sheet3 Transposed'!$1:$1 with a abslout refrence just change it back to ='Sheet3 Transposed'!1:1 with removing $ dollar signs.

03 - Copy the the drop down cell D2 and paste it in the range D2:D1001, we get Error: Invalid Input must fall within specified range, in the whole range D2:D1001, to solve it copy the range 'Sheet3 Transposed'!A1:A1000 and in D2 right click: paste special > Values only

enjoy :D