I'm looking to change out a column of data in my excel spreadsheet with 8760 data points without copy and paste. I'd like to use a drop down menu to select various data set columns in other tabs of the workbook which will auto populate the column. How might I go about this?

  • I'd say that either you have a macro that populate the column, as a function can't change any cells other than itself, or you fill your column with a function that takes the value from the set, based on cell position and value chosen in drop down. If you provide an example on how the dat ais structured, I could provide an example on how to do it. – Christofer Weber Apr 26 '19 at 9:58

To create your own drop-down list for some cell, do the following:

1.    Enter the list of items in a range.
2.    Select the cell that will contain the drop-down list.
3.    On the Data tab, in the Data Tools group, click Data Validation
4.    In the Data Validation dialog box, on the Settings tab
      In the Allow drop-down list, select List.
      In the Source box, specify the range that contains the items (ex: =$A$1:$A$3).
      Make sure that the In-Cell Dropdown option is checked.
5.    Click OK.
| improve this answer | |
  • This doesn't quite answer my question. I'm able to create a drop down of random variables but how can I go about auto-filling the 8760 data points in the column below (the reason for creating the drop down)? – user1027222 Apr 25 '19 at 12:54

It's hard to give specifics without examples, but here is one way to do it. Say that this is your data on sheet2: Example database

And so on.

Then on sheet1 you have a data validation list where you can choose the different lists: enter image description here

Then you can fill the range you want to populate with a function to search and retrieve the right value.
There are are a few ways to do this. You could do a very controlled nested if like this:


For the first cell, and then copy down. But this is not a very graceful or dynamic way of making it work.

A different approach would be using the classic index match approach:


For the first row, and then copy down.
MATCH searches row 2 in sheet2 for anything matching the drop-down list (here in C2), and returns the column number.
INDEX then returns the value of the cell in the specified row (3 being the first row in my example) and column returned by the MATCH function. IFERROR makes sure that the cell is empty, should the drop down box be empty.

This then looks something like this: enter image description here

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.