Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Mar 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro (Excel) (2002)

    I have a question about Excel, Macro function). I like it incrementally increases one cell for graph data source formula each month. For example, I like =SERIES(,'Raw Data'!$B$5:$N$5,'Raw Data'!$B$7:$N$7,1) to =SERIES(,'Raw Data'!$C$5:$O$5,'Raw Data'!$C$7:$O$7,1) .
    Thanks.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro (Excel) (2002)

    Instead of a macro, I suggest a "Dynamic Range Name" which is created with OFFSET function. GIve each of these ranges a name and use the name in the chart. You will OFFSET the column based on the current month, automatically changing the range each month without a macro.
    John Walkenbach has an example of this technique. His example expands the rows, but it is the same principle to expand the columns: you will just base the "offset column" on the date.

    Past back if you have any questions on it.

    Steve
    PS Welcome to the lounge.

  3. #3
    New Lounger
    Join Date
    Mar 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro (Excel) (2002)

    Thanks very much. I have tested it and it works. However, the only problem is I only need moving 12 months data for my chart. In another words, I only need data from March 2003 to Febuary 2004. Next month I need data from April 2003 to March 2004 instead of March 2003 to March 2003. How can I delete the first month data every time? Thanks again.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro (Excel) (2002)

    Going back to your original question. changing:
    =SERIES(,'Raw Data'!$B$5:$N$5,'Raw Data'!$B$7:$N$7,1)
    to
    =SERIES(,'Raw Data'!$C$5:$O$5,'Raw Data'!$C$7:$O$7,1)

    I will assume that B5:N5 and B7:N7 are the X and Y ranges for Feb
    and that C5:O5 and C7:O7 are the X and Y ranges for Mar

    That puts Jan's X&Y in A5:M5 and A7:M7 It also follows that D, E, F etc are starting for April, May, June, etc. X's are in row 5, Y's in row 7, and you want to plot 13 months. The first month will be determined by the current month.

    Create the following named ranges (Insert- name -define)
    Name:
    CurrentOffset
    Refers to:
    =MONTH(NOW())-1
    <add>
    Name:
    XCurrent
    refers to:
    =OFFSET('Raw Data'!$A$5,0,CurrentOffset,1,13)
    <add>
    Name:
    YCurrent
    refers to:
    =OFFSET('Raw Data'!$A$5,2,CurrentOffset,1,13)
    <ok>

    Create the chart to use the XCurrent and YCurrent for the ranges. Now when the month changes, your range will change automatically.

    If you don't want the month to change automatically based on the date, you could also link it to a cell where you enter in the desired month, or create a combobox list of month names and then get the month number and use that. For example if you put the month number in 'Raw Data'!A1 (either manually or as a linked cell in comboboz) you could define:
    Insert-name -define
    Name:
    CurrentOffset
    Refers to:
    ='Raw Data'!$A$1-1

    And you could get any month by selecting a different one.

    Hope this helps. If not post back with more specific questions.

    Steve

  5. #5
    New Lounger
    Join Date
    Mar 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro (Excel) (2002)

    Thanks very much for your answer, Steve. It works exactly as I want. My boss gave me high credit.
    I have a follow up question. I copyed the working hours for every month (12 month) from raw data worksheet to the row below each chart. How can I apply for dynamic range to the working hours row so that next month 12 month data will move one cell ahead automatically (I only need to change CurrentOffset)? I will be highly appreciated if you can write the formua for me again. You are great help.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro (Excel) (2002)

    I am not sure what you mean. So I will explain the formula The example I provided:
    <pre>=OFFSET('Raw Data'!$A$5,2,CurrentOffset,1,13)</pre>

    Starts at 'Raw Data'!$A$5 goes 2 rows down, goes the value of CurrentOffset columns to the right. From that it creates a range that is 1 row "deep" by 13 columns long.

    You can change any of the items. Many I hardcoded, but all can be references if desired.

    Hope this helps. if not, could you be a little more specific about what you need?

    Steve

  7. #7
    New Lounger
    Join Date
    Mar 2004
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro (Excel) (2002)

    Thanks for your answer, Steve. I know I have not explained my question clearly. Let me try one more time. This is nothing to do with chart. Right below every chart, I have a row with numbers of every month for total 13 months. Let's say, from Feb 2003 to Feb 2004. Next month, I only need data from March 2003 to March 2004. The data can be linked from Raw data. There are 200 charts and 200 rows. I hope with one click (Using dynamic range or macro) next month, all rows will be automatically updated to March 2003 to March 2004 number. Thanks again.

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro (Excel) (2002)

    If I understand, create the dynamic range name to grab the data.
    Assume you want to place the numbers in D9-09, and then named range is MyNewRange.

    In D9 enter:
    <pre>=index(mynewRange,column()-column($d9)-1)</pre>


    Copy this from D6 to e99

    If i misunderstood or didn't explain well, please post back.
    Steve

Posting Permissions

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