Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Chicago, Illinois, USA
    Posts
    187
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Formula output to chart range (2K SR-1)

    I'm using the following formula to extract the address range from a table of values ("dailyDATA" which is sorted by date) of those line items listed within a one month period (as selected from a drop-down).

    =CELL("address",INDEX(dailyDATA,MATCH(AC3,$B$37:$B $537,0)+1,1))&":"&CELL("address",INDEX(dailyDATA,M ATCH(AD3,$B$37:$B$537,0)+1,1))
    which outputs as $B$221:$B$235

    The goal is to have a chart that used the output of the formula as a series range. I'm stuck on getting the chart to recognize it as such. I'm sure I'm overlooking something simple.

    Any help would be appreciated.

    Thanx in advance

  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: Formula output to chart range (2K SR-1)

    <P ID="edit" class=small>(Edited by sdckapr on 13-Oct-03 15:07. Corrected a mistake in formula)</P>Create a named range that refers to (one line):
    =OFFSET(dailydata,MATCH(Sheet1!$AC$3,Sheet1!$B$37: $B$537,0),0,1+MATCH(Sheet1!$AD$3,Sheet1!$B$37:$B$5 37,0)-MATCH(Sheet1!$AC$3,Sheet1!$B$37:$B$537,0),1)

    Then use that named range as the chart range you desire.

    Explanation on OFFSET function parameters used:
    Starts at dailydata (upper left cell)
    goes down this many rows:
    MATCH(Sheet1!$AC$3,Sheet1!$B$37:$B$537,0)
    and over 0 columns
    The range is:
    1+MATCH(Sheet1!$AD$3,Sheet1!$B$37:$B$537,0)-MATCH(Sheet1!$AC$3,Sheet1!$B$37:$B$537,0)
    rows "long"
    and 1 column wide.

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Chicago, Illinois, USA
    Posts
    187
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Formula output to chart range (2K SR-1)

    Thanx for your quick response but I'm still having trouble with it. Please check out the attached sample. Check out the table at W2:AF14. I can get columns AC and AD to return the correct ranges for the months being queried. The drop-down should select the correct address and put the label range in AC2 and the series range in AD2. Using the OFFSET function keeps returning a date or a #VALUE! error. This shouldn't be this hard.

  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: Formula output to chart range (2K SR-1)

    That offset formula you have yields a RANGE object which refers to the range. It is an ARRAY of values, if you try to put it into one cell it will just give the first value, or if the range has no elements (like for Nov 2002) it will give #na. The formula should be in the defined name not in a cell.

    It should probably be (based on your setup):
    =OFFSET(dailyDATA,RowStart+1,0,RowCount,1)
    since dailydata has a header in the row.


    If I understand, what you want to do is:

    Insert - name - define
    Name: XRange
    Refers to: =OFFSET(dailyDATA,RowStart+1,0,RowCount,1)
    <add>
    Name: YChange
    Refers to: =OFFSET(dailyDATA,RowStart+1,3*'DAILY AVG'!$Y$1-2,RowCount,1)
    <add><close>

    Right click chart
    Source data - series tab
    series: Change over time
    Values: =sample.xls!YChange
    Category X-labels: =sample.xls!Xrange
    <ok>

    Now when you change date pulldown and/or other pulldown you will change the range based on the offset values.
    For example Feb '03, 'Wire ties' will plot:
    X range: B57:B76
    Yrange: I57:I76 (Change)

    If this is not what you want could you be a little more specific about what you need.

    Steve

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Chicago, Illinois, USA
    Posts
    187
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Formula output to chart range (2K SR-1)

    The problem is that the OFFSET function returns #VALUE!.

  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: Formula output to chart range (2K SR-1)

    It is an ARRAY/range of values. If you confirm it with ctrl-shift-enter you will get rid of the #value, but it will only display the first cell in the range since you only put it into 1 cell.

    I guess I am confused: What do you want to do with this range of values?

    I thought you wanted that range to be used as the range in a chart?
    I thought:
    You want to change the date pulldown and have the chart plot those dates from column B in the "dailydata" range as the X range
    And based on the other pulldown (Mud Units,Dedicated molds, or Wire ties)
    Choose those same "date rows" from column C (MUD units), column F (Ded Units), or col I (wire ties) for the Y range

    If that is so, follow my directions and you will plot that output onto a chart.

    If you do not want to use that range in a chart, you will have to be more specific about what you want to do, as I must have really misunderstood your question. If that is the case, offset might not be want you want to use at all.

    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
  •