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.

Cheers,

Matt

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.
Jerry

Jerry that is fantastic!

Thanks for the extra detail of the inner workings of the code too, it will help when I am re-applying it to the real spreadsheet.

Your a lifesaver

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.

Jerry this is my substitue code:


Private Sub cmdUpdateList_Click()
Dim strTargetKey As String, strTemp As String
Dim c As Integer, d As Integer, e As Integer

strTargetKey = Cells(23, 5).Value & Cells(3, 12).Value & Cells(3, 13).Value

c = 29
Do While Cells(i, 3) <> ""
Cells(i, 3) = ""
Cells(i, 4) = ""
Cells(i, 5) = ""
c = c + 1
Loop

i = 4
j = 29

Do While Sheets("Inspections").Cells(i, 9).Value <> ""
strTemp = Sheets("Inspectiions").Cells(i, 11).Value & _
Sheets("Inspections").Cells(i, 9).Value & Sheets("Inspections").Cells(i, 10).Value

If strTemp = strTargetKey Then
Cells(j, 3).Value = Sheets("Inspections").Cells(i, 2).Value
Cells(j, 4).Value = Sheets("Inspections").Cells(i, 5)
Cells(j, 5).Value = Sheets("Inspections").Cells(i, 8)
j = j + 1
End If

i = i + 1

Loop

If j = 29 Then
MsgBox "Computer Says No!"
End If

End Sub

As you can see in the real version I am returning 3 values into the output instead of 2.

However, I'm not sure I have referenced the other sheet "Inspections" correctly and I am also having huge probelms getting the code to apply to the button...essentialy I just need to know how to do it



Also thanks Colin,

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.

Hi Matt,
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.

Cheers

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.
Good luck.

Hey jerry good Morning,

Had another stab at this this morning and your guidance has gotten me further along the path, thanks.

The key difference between your version and mine is that in VBA you have the "General" and "Declarations". While you have cmdUpdateList in the General tab, mine is under declarations.

Also, when I hit the control button now I get a error 400 message.

Any ideas? Ignore...

Well, how embarrassing. While I had assigned the macro wrong, and thank you so much for showing me the proper way, my code had basic errors with .Value not being where they should.

Sorry for the confusion the code is now working perfectly and doing exactly what I need it to. Thanks guys, I really appreciate this one.