Charting from advanced filter

[kingsp]

Hi there. I have a filter to allow a user to search rows within a date range.

This works fine in terms of the result list however the charts I have set up retain all of the original data. I am using the Counta formula on another sheet to provide the data for the charts.

The row numbers of the search result turn blue and I just presume the other rows are hidden as the numbers are out of sequence. I have tried some examples to try and copy the filtered list to another sheet however was unsuccessful. How can I copy the filtered data to another sheet to avoid all the original data being counted?

Hope my question isn't too annoying I am very new to this.

Many thanks

Simon.

[cobwebs]

Please provide a sample file

[kingsp]

Apologies -- sample file that I am working on is now attached

[cobwebs]

Hi mate,

Please find a sample attached. Hope this helps you out

[kingsp]

Thank you very much cobwebs for taking the time to look at this. I can see how your solution will work and am delighted! - however I am a bit confused with where I need to put the Offset formula and what exactly I need to create and do in terms of the named range. Thanks again for your help it is much appreciated.

[cobwebs]

In the example I sent you, I put a dynamic named range in column L in sheet “Search”

To do this select Formulas > Name Manager > New
- Type a name for your range – I chose obviously “alcohol”, which is the heading for column “L”
- In the “Refers To:” section type the formula =OFFSET(Search!$L$21,0,0,COUNTA(Search!$A:$L),1)
- Click OK button
- As you move across each column, creating new named ranges, change the name and formula to suit

On the “Charts” sheet
- In my example in cell A7, under alcohol type the formula =SUBTOTAL(3,'Sample File(2).xls'!alcohol)
- As you move across each column, change the formula to suit i.e replace alcohol with drugs etc

Hope this helps, if so, please mark "solved" and "add reputation"

Regards,

Cobwebs

[kingsp]

Works perfectly. Great piece of learning for me also. Many thanks Cobwebs.

[cobwebs]

Welcome mate.

Have you marked this as solved, you will need to go back to the original request, advanced etc, and can I ask you to click on the Add Reputation button as well? Cheers

Cobwebs

[kingsp]

Hi Cobwebs. I had already done both - solved and added rep -- many thanks again.