Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Location
    Brisbane Australia
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update Autofiltered List

    Excel 97
    I have helped a friend build a workbook to graph data for a number of production sites. Basically we want a separate chart for each day of the week for each site and the data has to be maintained by somebody else.

    What we setup was a main database sheet where the data was entered and we hid a sheet for each day of the week [Monday to Friday] which read the info from the database sheet and used AutoFilter to only show a particular day of the week. We then produced the charts for each site from the Filtered list.

    The problem is that when new data is added to the database sheet the AutoFiltered lists don't dynamically update the data for the charts. I can't figure a way to get the data to refresh short of using a macro which is really hard as these sheets are actually protected and hidden to stop anyone upsetting the chart data.

    Is there a way to make this work or is there a better way to get the result we are after? The AutoFilter works well but is not suitable if it doesn't reflect new data being added daily.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Update Autofiltered List

    What you need is a named dynamic range for the data so that as it grows the reference expands to include the latest data. Try the following threads for a further descriptions

    Graphing and <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=xl&Number=35566&page=&view= &sb=&vc=1>Named Range Automatically expand </A>

    Andrew C

  3. #3
    Star Lounger
    Join Date
    Jun 2001
    Location
    Brisbane Australia
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Autofiltered List

    Thanks Andrew. I like the Automatically expanding named ranges - I think I can get a lot of use from that feature. I don't think it has resolved my issue with the problem I described though. Maybe it does but I think I need to do something differently with my data.

    Basically the database sheet has 2 pieces of data [numerical info] for each of 6 states here in Australia. This data tracks for each business day of the week [Monday to Friday] except for local holidays which are not always the same in each state.

    I therefore have a date and 12 pieces of data to analyse. I want to produce 5 charts for each state and look at the data for the "day of the week". This means that I have a Monday chart and a Tuesday chart and so on. I used the AutoFilter so I could filter out the unneccessary days and have a Monday chart with no gaps. This is where I think I am going wrong as the expanding named range doesn't help me here. It would all work fine the way I have it except the AutoFilter doesn't refresh by itself.

    Is there a better way to extract the daily data I need than what I have tried so far? I haven't attached a sample cause the file is pretty large although I could cut some data from it if it would help.

    Thanks

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Update Autofiltered List

    Hi John,

    Have you explored the possibility of Pivot Tables and Pivot Charts rather than autofilter - it might be the way to go.

    If you could post a sample of your data (dummy data of course) and the required outputs, I think it would help in finding some solution.

    Andrew C.

  5. #5
    Star Lounger
    Join Date
    Jun 2001
    Location
    Brisbane Australia
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Autofiltered List

    I actually tried Pivot tables first but I couldn't get it to just "filter" - it always wanted to sum [or whatever function I selected] rather than just displaying say al Mondays data.

    I'll grab a sample of the data at work tomorrow [after 11 pm here at the moment] and post a sample for you.

    thanks

  6. #6
    Star Lounger
    Join Date
    Jun 2001
    Location
    Brisbane Australia
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Autofiltered List

    OK. I've attached some sample data from the spreadsheet. I've just copied it out of the main file and haven't bothered to change the cell references on the "Monday" or the "WA Charts" sheet. I'm sure you will see what we are doing. The "TST Database" sheet is where we want other people to populate the data. The "WA Charts" are the outputs we are seeking. There will be 6 of these pages [one for each state] which contain a daily chart for each day of the week and a chart for the number of branches.

    At the moment we are using the "TST Database" sheet to feed 5 hidden pages [Monday to Friday] where we have AutoFiltered the data to only show the "day" we are interested in. We then populate the 6 chart pages with the data from the respective columns on these hidden sheets.

    Doing it this way has certainly "grown" the file larger than we would have liked [its currently over 3 megabyte]. We have then struck the problem with the AutoFilters not updating dynamically as more data is added to the "TST Database" sheet.

    Anything you can suggest would be appreciated.
    Attached Files Attached Files

  7. #7
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Update Autofiltered List

    John,

    Attached find an amended version (on just one sheet for the moment) of your file, using a pivot table and chart to do what I think you want. I inserted 2 new calculated columns to help, descriptions on the worksheet. If it is close enough then maybe some event procedures could be written to automate things even more. There are no macros at present.

    See what you think

    Andrew
    Attached Files Attached Files

  8. #8
    Star Lounger
    Join Date
    Jun 2001
    Location
    Brisbane Australia
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Autofiltered List

    That looks great Andrew. I think you have shown me sonething about Pivot Tables I haven't discovered before. I'll try reformatting the entire table at work on Monday and see how it looks.

    What are these "event procedures" you mention though? How do they effect the spreadsheet?

    Thanks

  9. #9
    Star Lounger
    Join Date
    Jun 2001
    Location
    Brisbane Australia
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Autofiltered List

    Hi Andrew

    I modified the spreadsheet today to replace my use of flitered data with pivot tables. I now have the database sheet, 6 sheets containg the respective states graphs, and 5 sheets each containing a separate pivot table for the relative day of the week that feeds the data to the charts.

    I still have the problem now of how to "refresh" the pivot tables to update the data on the graphs without having the user individually refresh each one. Is this what you meant by the "Event Procedure"? I have been trying to find out a little more about this and will be looking around the lounge to see if the information is already there.

    What do you suggest?

    Thanks

  10. #10
    Star Lounger
    Join Date
    Jun 2001
    Location
    Brisbane Australia
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Autofiltered List

    Alright, I couldn't help myself. I've been playing around trying to get this to work. I added the following code to the Worksheet for one of the Charts sheet:

    Private Sub Worksheet_Activate()
    Application.ScreenUpdating = False
    Set pvtTable = Worksheets("Monday").Range("c5").PivotTable
    pvtTable.RefreshTable
    Set pvtTable = Worksheets("Tuesday").Range("c5").PivotTable
    pvtTable.RefreshTable
    Set pvtTable = Worksheets("Wednesday").Range("c5").PivotTable
    pvtTable.RefreshTable
    Set pvtTable = Worksheets("Thursday").Range("c5").PivotTable
    pvtTable.RefreshTable
    Set pvtTable = Worksheets("Friday").Range("c5").PivotTable
    pvtTable.RefreshTable
    Application.ScreenUpdating = True
    End Sub

    It probably isn't very elegant but it "sort of" works. I can update the data as much as I like on the Database sheet - as soon as I select one of the Chart sheets it refreshes all of my pivot tables. That is great. Even when they are hidden the PT's are still refreshed. When I "Protect" the sheets though I get an error.

    Is there a better way of doing what I am trying to do and how can I protect the sheets and still have this work?

    Thanks

  11. #11
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Update Autofiltered List

    Hi John,

    You need to include something like the following in your code before any changes are attempted :

    ActiveSheet.Unprotect Password:="PASS", where PASS is whatever password has been applied.

    When you have finished the changes re-protect with ActiveSheet.Protect Password:="PASS"

    If the Password is sensitive, it could of course be viewd by users unless you protect the module containing the code.

    The code is fine, but do you need a table for each day, when you can include the day in a page field, and just use the page field for viewing rather than a seperate sheet. Just a thought .

    Andrew

  12. #12
    Star Lounger
    Join Date
    Jun 2001
    Location
    Brisbane Australia
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Autofiltered List

    Thanks for that Andrew. I know that I could get away with a single sheet with a single Pivot-table and a single sheet of graphs. This was just so it all happened in the "background" so to speak and no interaction was necessary from the User apart from the Data input.

    Also, if we only have the one PT that shows just one day we cannot print out a single page for each state that shows a graph for each day of the week at the same time. What we get now is a single page for WA that has a graph for Monday, Tuesday, Wednesday, Thursday, and Friday as well as a graph of the number of Branches as at Friday for the complete data cycle. I don't think the single PT would give us that.

    Thank you

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •