Use Names for Branch Select in "Inventory Valuation by Branch"

Rather than using a list of Branch numbers for the drop down selection you might prefer to use the actual Branch Names.  You could write another query to pull the Branch Name (or City) and the Branch Number but since Branches do not normally change frequently you could just manually enter the list in a worksheet.  Either way you choose you should end up with something similar to this.

As we've done previously you will need to name the range of cells containing your list.  The list of Branch Names has been named nameBranch.  This range will be used for the data validation.  Additionally select all the names and numbers (i.e. in this example you would select A2:B14) and name it dataBranch. 

Before changing the source used in the drop down you'll need to change how the parameter value is passed to the query; if it's not changed and the value in the drop down changes from a numeric value to an alpha value an error message will display.  Open the Parameters dialog window and select "Prompt for value using the following string:" and click OK.

On the worksheet where the Branch number is currently being selected click on the cell that has the drop down list.  Display the Data Validation dialog box and change the entry in the Source box from =validBrNum to =nameBranch (or whatever you named your list of Branch Names) and click OK.  Now the drop down will display the list of names rather than numbers.

Now select a cell to contain the Branch number to be passed as the parameter to the query.  I recommend using a cell in a row or column that can be hidden so nonessential data is not displayed on the worksheet (I've chosen J6).  In your selected cell enter =VLOOKUP(G3,dataBranch,2,0) where G3 is the cell containing the Branch Name.  The Branch number should now display in the cell.

Now reopen the Parameter dialog box and change it to use the cell where the Branch number is being displayed and to refresh when the cell value changes and then click OK.

Now the user will be able to select the desired Branch by Name rather than Number.

As always if you have any questions about this post or anything related to Excel leave a comment here or email me at the office.