Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lookups & Charts (2002)

    Hello Lounger

  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: Lookups & Charts (2002)

    You need to create dynamic range names
    Insert - Name - define the following names
    <table border=1><td valign=bottom>Name</td><td valign=bottom>Refers To</td><td valign=bottom>rDataStart</td><td valign=bottom>=Sheet1!$C$3</td><td valign=bottom>rDataHeader</td><td valign=bottom>=OFFSET(rDataStart,0,1,1,256-COLUMN(rDataStart))</td><td valign=bottom>rDataStartCol</td><td valign=bottom>=MATCH(Sheet1!$C$18,rDataHeader,0)</td><td valign=bottom>X_Dates</td><td valign=bottom>=OFFSET(rDataStart,0,rDataStartCol,1 ,12)</td><td valign=bottom>Y_Bolts</td><td valign=bottom>=OFFSET(rDataStart,3,rDataStartCol,1 ,12)</td><td valign=bottom>Y_Nuts</td><td valign=bottom>=OFFSET(rDataStart,2,rDataStartCol,1 ,12)</td><td valign=bottom>Y_Screws</td><td valign=bottom>=OFFSET(rDataStart,6,rDataStartCol,1 ,12)</td><td valign=bottom>Y_Washers</td><td valign=bottom>=OFFSET(rDataStart,4,rDataStartCol,1 ,12)</td><td valign=bottom>Y_Widgets</td><td valign=bottom>=OFFSET(rDataStart,5,rDataStartCol,1 ,12)</td></table>

    Right click the chart, Source data ... , series tab
    Enter into categroy X- Axis:
    ='Sheet1'!X_Dates

    Then select for each Series (eg Nuts) in Values enter:
    ='Sheet1'!Y_Nuts

    select Bolts and enter:
    ='Sheet1'!Y_Bolts

    etc, etc
    rDataStart, is the upper left cell of the data
    rDataHeader, is the range from the rdatastart till the end of the sheet
    rDataStartCol is the column of the rDataHeader row, which matches the selection in C18 (the date selector)
    X_Dates - Y_Widgets define the range from the start col to 12 more cols for each row

    As you change C18 (the date), the rDataStartCol value will change, which changes all the defined ranges. Since the chart is based on the defined names, the chart automagically updates

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Dec 2003
    Location
    Perth, Western Australia, Australia
    Posts
    128
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Lookups & Charts (2002)

    Steve, Sorry about the delay in thanking you for your help - it works just fine!!

    Thanks

Posting Permissions

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