+ Reply to Thread
Results 1 to 11 of 11

Automatically charting data on a weekly basis for a rolling 12 month period

  1. #1
    Registered User
    Join Date
    02-09-2011
    Location
    philly, pa
    MS-Off Ver
    2013
    Posts
    27

    Automatically charting data on a weekly basis for a rolling 12 month period

    Hi all,

    I would like to create a chart where date is the X axsis and $ value is the Y axsis.

    The $ value cell (i3) is comprised of many variables and changing on a daily basis.

    Is there a way for excel to automatically plot $ value points on a chart on a weekly basis (i.e. every Tuesday) for a rolling 12 month period?

    Thanks in advance!!!!

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Automatically charting data on a weekly basis for a rolling 12 month period

    Simplest approach is to use formula to build a table of contiguous charting data from your main data table.

    If you need more help post example workbook so we can see your data and it's layout. Also include an example of the chart type you want to create
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    02-09-2011
    Location
    philly, pa
    MS-Off Ver
    2013
    Posts
    27

    Re: Automatically charting data on a weekly basis for a rolling 12 month period

    Hi Andy,

    The file contains a lot of personal info, so i made a copy of the individual workbook and attached it.

    I would like to have 3 charts:

    Chart 1: cell i3, open case opportunity

    Chart 2: Cell i10, total GDC in transition

    Chart 3: cells g3-g6, using labels a3-a6


    any basic line chart will do. x axsis=date y axsis=$value

    THANKS!!!!!!
    Attached Files Attached Files

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Automatically charting data on a weekly basis for a rolling 12 month period

    Sorry, your example file does not make any sense.

    Unless of course you meant to describe is a workbook that refreshes cells, such as I3, and you want to keep a history of I3 values for a 12 month period and chart that. Is that want you mean/want? If so you will need vba code to output I3 value to a static range of cells which are then charted.

  5. #5
    Registered User
    Join Date
    02-09-2011
    Location
    philly, pa
    MS-Off Ver
    2013
    Posts
    27

    Re: Automatically charting data on a weekly basis for a rolling 12 month period

    Andy,

    I understand your confusion. All of the cells I am looking to chart change in value on daily basis, sometimes multiple times a day. they refresh themselves every time the raw data changes, using simple functions.

    I believe your response is point on for what I am looking to do.

    Would it be possible for the vba code to automatically output values of these cells, 1 day every week, for a rolling 12 month period, then chart this data????


    Thanks a ton.

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Automatically charting data on a weekly basis for a rolling 12 month period

    Rolling 52 weeks.

    This will run a macro on file open. If it's a tuesday the values will be appended to a Listboject which will automatically update the chart.

    Please Login or Register  to view this content.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    02-09-2011
    Location
    philly, pa
    MS-Off Ver
    2013
    Posts
    27

    Re: Automatically charting data on a weekly basis for a rolling 12 month period

    Andy,

    When I enter the code into visual basic nothing happens??

    If you wouldn't mind walking me through how to execute that would be great.

    I appreciate all of the help.

  8. #8
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Automatically charting data on a weekly basis for a rolling 12 month period

    The code needs to be in the Thisworkbook object. As it is using the Workbook_Open event.

    The code will not actually do anything unless you open the workbook on a Tuesday, as per your request. If you want to test the code then comment out the section that tests for day of the week. You can the place the cursor in the event code and press F5 to execute it.

  9. #9
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Automatically charting data on a weekly basis for a rolling 12 month period

    or
    Please Login or Register  to view this content.



  10. #10
    Registered User
    Join Date
    02-09-2011
    Location
    philly, pa
    MS-Off Ver
    2013
    Posts
    27

    Re: Automatically charting data on a weekly basis for a rolling 12 month period

    ok great, it worked.

    Now, if I open the document 5 times on tuesday, there will be duplicate data... How do I prevent duplicate data?

    Thanks to both of you!

  11. #11
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Automatically charting data on a weekly basis for a rolling 12 month period

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1