Add Branch Select to "Inventory Valuation by Branch"

Continuing with the development of "Inventory Valuation by Branch" we'll add the ability for users to select valid Branches from a drop down.  First we'll look at how to create this list using Data Validation.  If you're only dealing with a few Branches the list can be entered directly in the "Data Validation" dialog box but because other lists that you may want to use will need to be updated we'll add the valid Branches to a worksheet and name the range of cells containing the list.

To begin add a new worksheet to the "Inventory Valuation by Branch" workbook.  In cell A1 type validBrNum.  Beginning in cell A2 enter your valid Branch numbers.  When all Branch numbers have been entered select all the cells containing the numbers and then click in the Name Box (the box immediately above column A) and by validBrNum.  This names the selected range of cells.

On the worksheet containing the data select the cell that you contains the Branch number linked to the query.  If you're using Excel 2007 select the Data tab on the ribbon and click Data Validation in the Data Tools section (for 2003 click Data > Validation).  The Data Validation dialog box will display.  In the Allow box click the drop down and select List.  In the Source box type =validBrNum and verify that "In-cell dropdown" is checked.

Click OK.  Now the cell containing the Branch number has a drop down and when it's clicked the user can select from the list of Branches and when selected the query will run and display data for the selected Branch.

In the next post we'll look at using a list of valid Branch Names rather than Numbers.

As always, if you have any questions about this post or Excel in general leave a comment here or email me at the office.