Unanswered: [SOLVED]Stumped! Looking for another way of autofiltering
Sorry guys not sure what to title this one as I have no idea what the solution might be at this stage.
I have a large table of information and two cloumns are year and month. In the attached file I have simplified the spreadsheet using an analogy with Animals, Names, Location, Year and Month.
In the original spreadsheet we have a reporting table that reports on all sorts of data from the table by selecting Year and Month. In my analogy, we may be interested in how many Animal sightings were seen in Jan 2009.
What I would find extremely useful is if someone could show me how to achieve this:
In the output table in the attached file, list the relevant data that meets the specified Year, Month, and location in cells J3:J5. Should the values in these cells change the list should also update accordingly.
Essentially i want to recreate the autofiltering function in a table without having to use the standard method. This is useful because I could then palce this output into a reporting template.
Last edited by MatthewM; 01-24-11 at 00:07.
Matt, I believe the only user-friendly solution to this is to use VBA. This allows you to update the output list by clicking on a command button after the three criteria are entered.
Attached is your Excel worksheet with a command button added and VBA code that formulates a key from the cells under "Timeframe",for example "AJan2009", then a search is done for a combination of values in columns D, E, & F. When a match is found, the output list is populated with the values found in columns B and C.
This is possible without VBA, but it is a bit fiddly. You can set up a querytable with a parameterised query which automatically refreshes when the parameter ranges are changed. You already have a solution from Jerry, but if you are interested in this option then I can give you a step-by-step guide to getting it set up.
Another alternative is to use the advanced filter. However, to get the 'automatic refresh' you want, a little bit of VBA would be required for it. You would also have to slightly restructure your criteria range.
I've just got my head around Jerry code today and from that I could imagine your solution would be to create an array / table containing text combinations of my filter paramaters in a specific order?
If so, yeh that would work! Although I suspect this particular functionality might expand to 6 or 7 paramaters which could make this array much bigger.
There's no VBA and no arrays involved in the solution I proposed. It's just a query table with a query attached to it. The fiddly bit is getting the parameters in the query to reference cells in your sheet. You're happy with Jerry's solution so I'm not trying to give you the hard sell on this - I just mentioned it on the thread as a non-VBA alternative.
Matt, it sounds like you need the steps to link code to the button on your spreadsheet. Here are the steps you need to take. Assuming you know how to create a button with the Control Toolbox in the Visual Basic Toolbar,
- Click View, Toolbars, Visual Basic
- On the Visual Basic Toolbar, click on the Design Mode Icon (pencil/ruler/triangle) Clicking on this toggles Design Mode On or Off. Design Mode On allows you to change properties of the button, and the button will not execute code when you are in Design Mode.
- If you need to rename the button or change the wording on the button, right-click the button, choose Properties. You can rename the button, as I did to "cmdUpdateList", and you can change the Caption property which is the text that appears on the button.
- After giving the button the name you want to you use, right-click on the button and choose View Code. The VBA Editor will open up showing the procedure where you will insert all the code for this task. This step is what makes the procedure run when you click the button.
- When done editing the code, do a Save and return back to the worksheet.
- Click on Design Mode to toggle Design Mode off, and then you can close the Properties window and toolbars associated with Visual Basic. The next time you want to edit or view the VBA code, press Alt-F11 while viewing the worksheet with the button.
You should use Debug to test the values you are getting from the Inspections worksheet. Another way to test is to temporarily add a line of code following the lines where strTemp is assigned a value: MsgBox strTemp
When you click the button, a message box will display the values you are picking up.