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?
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.
And so on.
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
IFERROR makes sure that the cell is empty, should the drop down box be empty.