Filter for columns on another sheet

[howerd]

Hi,

I was wondering if there was a way of applying a 'filter' box on one spreadsheet which applies and adjusts a column on a different tab/sheet? I am not sure if there is an easy way of doing this, or whether a macro would need to be used. (Unfortunately I know nothing about Macros)

Any help would be great.

Thanks,

Chris

 

[baitmaster]

This is definitely possible using macros, but more info is needed

Please explain in more detail, what you are trying to do and why. I would keep everything within the same spreadsheet, its almost certainly not worth it to try and build this feature across multiple files... but let us know

 

[howerd]

Hi Baitmaster,

Currently I have a 'Data' tab which has a number of columns on which there are auto filters applying to column's 'A' and 'B'.

On another tab, I have a 'summary' sheet which has number of formula's analysing the information on the 'data' tab (which allow for when the filter applies using the subtotal formula)

Basically what I am after is on the 'Summary' tab, I was hoping to have 2 cells at the top, (B2 and B3 for example) which allowed me to filter the columns 'A' and 'B' on the 'Data' tab.

This will allow me to adjust the filters on the 'Data' tab, from the 'Summary' tab, which in turn will adjust the results/Analysis on the 'Summary' tab.

Does this make sense? If you need anything else let me know

Thanks for your help,

 

[baitmaster]

And is there a reason you are using the Autofilter, other than to adjust the results returned from your SUBTOTAL equation?

I ask because the answer to your equation would almost certainly be better handled with the right formula. In Excel 2007 or later I would suggest you probably want SUMIFS or COUNTIFS, and in Excel 2003 or earlier a well-written SUMPRODUCT would probably answer this. Both approaches would be much easier than using a VBA-controlled autofilter

 

[howerd]

Hi Baitmaster,

I am using Excel 2003, and in some of the formula's on the 'summary' tab I have used 'Sumproduct' formula' (example below) for my 'countif' formula, which I obtained from the forum:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Data!$G$3:$G$500,ROW(Data!$G$3:$G$500)-ROW(Data!$G$3),0,1)),--((Data!$G$3:$G$500>=6)*(Data!$G$3:$G$500<=10)))

along with some Subtotal formulas such as the below for Maximum, minimum etc.
=SUBTOTAL(4,Data!$G$3:$G$500)

This appear to demonstrate the results for the information which is visible on the Data tab (if the filter is applied)

So what I am after is just a way of the information which is visible on the Data tab to be controlled from the Summary tab, is there an easier way?

Thanks,

 

[baitmaster]

OK, looks to me like you're already well on the right lines with your formula, but don't yet have a full understanding of all that sumproduct can do, or perhaps how it is doing it - understandable, it's a very flexible formula, and often used in unexpected ways

Because of this, I think you've found something that sort of works for you, and are now trying to pull it in the wrong direction with the autofilter approach. Apologies if I sound like Im patronising you, not my intention

This link gives a very good overview of sumproduct.
Alternative link looks at array formulas, also very useful approach for what you are trying to do here

I think your end result needs to look something like this:
=SUMPRODUCT(SUBTOTAL(3,OFFSET(Data!$G$3:$G$500,ROW(Data!$G$3:$G$500)-ROW(Data!$G$3),0,1)),--((Data!$G$3:$G$500>=6)*(Data!$G$3:$G$500<=10)),--(Data!$A$3:$A$500=Summary!$B$2),--(Data!$B$3:$B$500=Summary!$B$3))

I'm not sure what the Offset is for, and I note you are trying to use COUNTA [i.e. subtotal(3, ...)]. I don't think I need to understand these bits for now, I'm assuming it works correctly for you. The section I have added simply asks 2 additional questions, like the autofilter approach would - is data column A = your first condition, and is data column B = your second condition

 

[howerd]

Not patronising at all, you hit the nail on the head!! I searched for a countif formula, which only counted visible/filtered cells, and that is the formula I found. I do not fully understand how it works and the links will be very useful thank you, I will read these at some point today, which will hopefully give me a better understanding.


Basically what I am after is the summary tab to be able to count the cells in different columns, if they meet certain criteria (in this case if the value is between 6 and 10). I need this to be based on all of the data, but as and when required, make it specific to criteria in A (Name) or B (Firm). So that the analysis results ont he summary tab can be applied to a specific name in column A, or a specific firm in Column B, when selected.

I have included the additions on the end of the formula in question and it returns a value of 0. (Instead of 2) If the autofilters are not being applied, I am not sure if the formula would need amending?

Apologies for my lack of knowledge on this!

Thanks again for your help.

 

[baitmaster]

OK, I can't see the reason for the offset, so I'd recommend you remove it. At least, if you need something which isn't covered by the following formula, you will be able to add it in simply using the principles from scratch, rather than start with a complex formula that you don't fully understand

I'd suggest you start with the following:
=SUMPRODUCT(--(Data!$G$3:$G$500>=6),--(Data!$G$3:$G$500<=10),--(Data!$A$3:$A$500=Summary!$B$2),--(Data!$B$3:$B$500=Summary!$B$3))

Since this returns results where all criteria are met, but you don't want to meet all criteria when e.g. name is not selected, you can adjust it like this:
=SUMPRODUCT(--(Data!$G$3:$G$500>=6),--(Data!$G$3:$G$500<=10),--(Data!$A$3:$A$500=Summary!$B$2),--(Data!$B$3:$B$500=Summary!$B$3)+($B$3=""))


This still isn't 100% right, because you may have empty cell values in your data, in which case there are occasions where [empty] data matches your [empty] filter criteria, PLUS you are adding in that your filter criteria = "", so you are double counting. You must ensure there is complete set of data in your data table for this change to work, or use a non-blank term such as "no filter", in your filter box, instead of empty value

You don't have to apologise for lack of knowledge! That's why the board is here...

 

[howerd]

You have been a great help baitmaster, thank you. (A case of trying to run before I can walk!)

Am I right in assuming that the same would apply to B2? So that '+($B$2="")' would need to be added also?

=SUMPRODUCT(--(Data!$G$3:$G$500>=6),--(Data!$G$3:$G$500<=10),--(Data!$A$3:$A$500=Summary!$B$2)+($B$2=""),--(Data!$B$3:$B$500=Summary!$B$3)+($B$3=""))

There will not be a blank cell in column A and B, so hopefully this will work a treat.

I have different formulas (currently using subtotal) which finds the max, min, average etc in different columns, which will also need adjusting, however, hopefully this will be covered on the links which you have kindly sent across, so I will go through these, unless it is relitively straightforward to adjust?

Thanks again for your help.

 

[baitmaster]

Positive feedback is always appreciated, thanks

Your assumption is correct

Min, Max etc won't work within sumproduct (well, I assume they don't, they might and I just don't know it... there's a lot of weird and wonderful things sumproduct can do). For this you will need the Array formulas link - another very useful and powerful branch of Excel formulas that you should swot up on

Example:
=MAX(IF(Data!$A$3:$A$500=Summary!$B$2,IF(Data!$B$3:$B$500=Summary!$B$3,Data!$G$3:$G$500)))
entered as an array formula by hitting shift + ctrl + enter, instead of just enter. You will know if you entered it correctly, because you will get curly brackets appear around it, you don't type them