To change the "Inventory Valuation by Branch" query to a parameter query you will need to edit the query. If you're using Excel 2007 right click on any cell in the data returned by the query and click Table > Edit Query. For 2003 right click on any cell in the data and Edit Query. In the Microsoft Query editor replace the value previously entered for IVNO10 with [brnum] as below...
Now when you run the query or return the data to Excel you will be prompted to enter a Branch number. Type the desired Branch number and click OK. This will either display the data for the selected Branch or will return the data to the worksheet.
Next we want to pass the Branch parameter from the worksheet back to MS Query when we want the query to execute and display data for a different Branch. This is done by designating a specific cell on the worksheet to contain the Branch number and then setting the query to refresh when the value of that cell changes. To do this in Excel 2007 right click on any cell in the returned data and click Table > Parameters to display the dialog box below...
Select the "Get the value from the following cell:" radio button and check "Refresh automatically when cell value changes" check box. Click in the box below "Get the value..." and then click in the cell on your worksheet that is to contain the Branch number. Click OK or simply press the enter key.
Now the user can easily go from displaying Branch 9 data...
To displaying Branch 99 data by changing the value in cell G3...
If an invalid value is entered in the cell the query will run but no data will be returned. To prevent this you can provide a list of valid Branch numbers from which the user can select the desired Branch.
The simplest way to achieve this is a drop down list containing a list of your Branches. This will be covered in the next post.
As always if you have any questions about this post or anything related to Excel you can post a comment here or email me at the office.
No comments:
Post a Comment