For example, Excel automatically assigned the name Table1_2 to the results table.
This will show you the current name and allow you to give it a different name if desired. To do this, select any cell in the table and then look at the Table Tools > Table Name field. In order to use the Name Manager, we first need to make a note of the table’s name. One option is to select all of the data cells in the table, and then enter the desired name into the Name Box (that little box just to the left of the formula bar).Īnother option is to use the Name Manager. There are a couple of options for accomplishing this step. We need to create a name that refers to the table that stores the query results. We are mostly done at this point, and all that remains is to use this list of choices in our drop-down. Our unique RepID list now appears in a table in our workbook, as shown below. Last, we need to return the results to Excel, so we use the Close & Load command. Now, we need to remove duplicates, so, we select the following command:įinally, we sort the list in ascending order by using the RepID drop-down. With the RepID column selected, we click the following command: So, let’s begin by removing the other columns. The results we want to return should be the RepID column, without duplicates, sorted in ascending order. This launches the Query Editor dialog, as shown below. Data > From Table (in the Get & Transform group).Then, we select any cell within the table and click the following command: If you are using a different version of Excel, Power Query may not be available or you may need to download the free Power Query Add-In.įirst, we navigate to the worksheet that contains our data table, shown below. Please note that the steps below are written with Excel 2016 for Windows. We’ll accomplish our objective by performing the following steps:
We don’t want to use VBA or to have to update any formulas going forward. Let’s see how Power Query can help. Our solution should be fast and easy to maintain over time, even when new reps may appear in the data table. We want the drop-down list to contain a unique list of reps from the table. On another sheet, we want the user to be able to select a rep from a drop-down. We have a data table that contains RepID, Date, and Amount columns, as shown below. However, the Power Query feature that’s built-in to Excel 2016 makes this process easier. This may sound familiar as we previously accomplished this with a PivotTable.
In this post, we’ll create a drop-down that contains a unique list of choices derived from a column that contains duplicate values.
0 kommentar(er)
