Selection by Branch, Region or ALL

Here's a way to use a Combo Box to select a Branch or range of Branches the result of which can be passed to an MS Parameter Query.

To begin enter data as below in a worksheet.

Select the cells from A2 to A18 and in click in the Name Box (immediately above column A) and type BranchList (no space between Branch and List) and press enter.

Now Select cells B2 to C18 and type BranchData in the name box.

This creates two named ranges that will be used to create the From and To Branch numbers that can be passed as parameters to MS Query.  All that we're doing with this table is creating a beginning and ending Branch Number.

To extract the beginning and ending numbers for a specific value in column A we'll use the INDEX function.  The arguments for the INDEX function are array (range of cells), row number and column number.  Our array is the range B2:C18 (which we've named BranchData), the beginning branch is in column 1 of this array and the ending branch is in column 2.  The row number is equal to the location of the entry in column A.  The formula to return the beginning branch for ALL would be =INDEX(BranchData,1,1) and the formula for the ending branch would be =INDEX(BranchData,1,2).  Since the beginning branch is always in column one and the ending branch is always in column 2 then all we need is a way to change the row value based on the selection from Column A (BranchList).  To accomplish this we'll use a Combo Box.

A Combo Box will allow us to return a numeric value to a specified cell based the selection made int the Box and also allows us to specify the range of cells to be used for the input.  For Excel 2003 click View > Toolbars > Forms to display the Forms Tool Bar.  In Excel 2007 click the Developer tab on the Ribbon and then Insert Forms.  Click the Combo Box icon and then on your worksheet click in the area where you want to place the Combo Box.  Adjust the size of the by dragging the corners or edges.  

Right click on the Combo Box and select Format control.  Click the Control tab and type BranchList in the Input range box.  Type $E$1 in the Cell link box.  Check the 3-D shading.  Click OK

In cell F5 type From Branch.  In cell G5 type To Branch.  In cell F6 enter =INDEX(BranchData,$E$1,1) and in cell G6 enter =INDEX(BranchData, $E$1,2).

Now when a selection is made in Combo Box the values under From Branch and To Branch should change to match the values in Columns B and C that correspond to the value in Column A