Excel Dynamic Chart #4: Advanced Filter

hello ,

i got a table in sheet 1 and in sheet 2 i got cretirias.


i want to use the advanced filter and record a macro that will filter the table according to these cretirias .


the problem is ,i need to suply a criteria range when i use the advance filter ,and my cretirias might vary from time to time.

for instance,it can be 2 rows criteria and can be only 1 row criteria. ( i can't choose a blank row for criteria cause the filter won't work and will give me the whole table).

how can i use a macro that will aut take the exact range of my criterias ?

thanks

I need to filter data which is 3 months from todays date or older. I'm going to use it in a macro which downloads the information from our accounts system.

I have set up the filter and it works fine when I manually put the Date in, but when I use the date 'formula' for todays date -3 months and reference the cell its in, it does not work. Here's what I have so far :

The filter from the macro :
Range("A:I").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Range("J1:J2"), Unique:=False

The way I work out the date 3 months from now (in cell K2) :
=DATE(YEAR(TODAY()),MONTH(TODAY())-3,DAY(TODAY()))

Cell J1 has the relevent Column Header
Cell J2 has the following formula :
=" < K2 " (there are no spaces I had to put them in so the post appeared correctly!)

Hi all,

In my 2003 workbook, the user fills in information using Excel's DataForm. One of the fields in the DataForm is a number - one to five (1-5). This information is held in A50:A100.

In a UserForm a ListBox is currently showing the five numbers which may be selected. This is then linked to an advanced filter for printing purposes.

Ideally what I want to happen is that the options will only appear in the ListBox if they have been previously inputted using the DataForm i.e. if the number '3' has not been inputted, the ListBox will only show "1,2,4,5".

Anyone know how this can be done?

Thanks.

I have a spreadsheet which I perform an advanced filter, but the problem I have is that when I get the results of the advanced filter I wish To filter further.
This causes the advanced filter to be removed. Is there some way of filtering the results of an advanced filter.

Thanks for you help

Des

Hello and thank you for giving this a try.

I have a spreadsheet where I use advanced filter to filter data. The filtered data is then printed via a macro. The number of rows changes each time the data is filtered. The print macro works great when the number of rows is 45 or less. When you get over 45 the data remains to be printed on one page. When you get to 150 rows, the font is almost too small to read - as it tries to print all the data on one page.

My question is can you modify the print macro to print 45 rows, and then start a new page of 45 rows, and so on.

Thank you.

Oh yes, the current print macro is run through a command button with the following code:
VB:

Private Sub CommandButton6_Click() 
    ActiveSheet.PageSetup.PrintArea = "" 
    ActiveSheet.PageSetup.PrintArea = Range("m1:r" & Cells(Rows.Count, "m").End(xlUp).Row).Address 
    ActiveSheet.PrintOut 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines

Hi all,

I'm trying to figure out a drop down control for a series of charts based on one table of data.

What I've been trying to do is have all the charts on one sheet, and the data table on the other. Currently, the columns are filtered such that if the filters are employed, the data displayed in the charts varies. That's pretty standard I think. The data encompasses several divisions within one company. The desire is to have a manager for any given division select their division name from a drop down box on the chart sheet, and display the chart for their specific division. So far I have been unsuccessful.

The data table is constantly being added to as the charts display a trend over time so named ranges do not work in this situation.

I'm thinking it would be something like a remote control filter. Where the drop down box on the chart page would control what the division column filter selects on the data table page.
Thus the filter would change to reflect the proper division instantly based on the selection.

Is that possible?

Thanks!

Neo

I have a spreadsheet which contains 1972 rows of data. The spreadsheet contains filters so a user can select to see their specific data (ie, geographic sales region).

The problem is that creating a bar chart of 1972 rows makes it unreadable. So I decided to chart only 30 rows of data. However, now when a user selects their region, it only shows the static 30 rows...meaning it cuts off all data that is in rows 31 through 1972. I'd like the chart to show a dynamic range based on the filter up to a maximum of 30 rows.

I've been reading up on named ranges using offset...but I'm new to this and don't fully understand how to adjust that formula to do what I want. That only seems relevant if I'll be adding more data to my sheet?

Thanks for any help or suggestions!

I am currently running Excel 2003 in Windows XP. This is my question. I have a database of products I'm storing on a sheet named Database, which the user never sees. On another sheet call Input, I have four drop-down boxes which allows the user to:
Step 1. Select products filtered by one of four choices. I have done this simply using a Data Validation List.
Step 2. The second drop-down contains another Data Validation List which uses a series of nested IF statements to filter the choices available in the list based on what was chosen in Step One.

Now is when it gets complicated

Step 3. The third drop down should only display those products which are valid choices based on steps 1 and 2. There are too many choices to use nested if statements. I can successfully filter the information I need using an Advanced Filter, but I don't know how activate it. I want it to filter the list of choices in the third drop-down as soon as a choice has been selected from the second drop-down.

Step 4. Needs to do the same thing as Step three, but now filtered based on all three previous choices.

Any help would be greatly appreciated.

I have a spreadsheet with a large number of graphs on and lots of data.

Most the graphs look across 20 or so columns and one or 2 rows down.

The problem is that l need to insert new colums in now, and obviously excel graphs dont recognise the inserted column data.

Therefore l believe using named ranges may solve this problem...

I have the formula =OFFSET(Sheet1!$A$1,0,0,2,MATCH("DC",Sheet1!$1:$1))

However i need to expand on this to achieve what i need to do.

1. Firstly l only want to look from column C onwards (so ignoring all data in column A and B)
2. I need the match to match to columns starting with the letters "DC", however there is a number after this, so it is not a direct match. The match needs to ignore columns with any other heading start.
3. is there anyway to automatically refresh the graphs to the named range once it changes, or any simble vb macro i can run?


I hope this makes some sence, anyhelp would be trully appreciated!

Many thanks in advance,

Matt

Hi,

I need help on Advanced Filter Criteria when i have recorded as a macro.

Below are the tables, If my cirteria is like below.

Customer Product AA Cookies

I will data extracted. However, if my data is as per the table below, where i need Custore as BB and Product as Milk with AA and cookies present. I will get all data related to AA and BB extracted. Please could some one help me on getting on BB even though if AA is present.


Customer Product AA Cookies BB Cookies BB Milk


Date Customer Product Total 2/2/2010 AA Cookies 2 2/2/2010 AA Cookies 2 2/2/2010 BB Cookies 3 3/2/2010 BB Cookies 3 3/2/2010 BB Milk 4 3/2/2010 CC Milk 4 4/2/2010 CC Milk 4 4/2/2010 CC Milk 2 4/2/2010 CC Brownies 2