Results 1 to 8 of 8
  1. #1
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    use CHOOSE to select different range (Excel 2003)

    I'm trying to create a single chart that dynamically switches its source data (w/o VBA) depending on a selection from drop down box. I was trying to use the CHOOSE() function but it doesn't seem to want to deal with dynamic range names.

    Example: cell $C$7 contains an index into a list of items (e.g., Michigan, Texas, Ohio) which are displayed in a combo box (from the Forms toolbar). The data for each of the selections, is actually a range of 'n' rows (rows of data for MI, for TX, for OH, etc.) I've named each data range ("rngMichigan", "rngTexas", etc.) and was trying to do this:

    CHOOSE($C$7, rngMichigan, rngTexax, rngOhio)

    where the chart would automatically use the rows of data for Michigan if the user selects the first item in the list, etc. (cell $C$7 contains the index of the selected item). Can this work or should I try something with INDIRECT (which I always have problems with).

    Thnx,
    Deb

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: use CHOOSE to select different range (Excel 2003)

    Hi Deb,
    Have a look at this post. It contains an example of using a drop-down to change the range the chart plots. Post back if you need further explanation!
    See post: <post:=466,223>post 466,223</post:>
    Regards,
    Rudi

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: use CHOOSE to select different range (Excel 2003)

    Does the attached do what you want? I defined named ranges for each of the chart series.

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: use CHOOSE to select different range (Excel 2003)

    yes this is exactly what I wanted to achieve <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>. I couldn't figure out the exact dynamic formula and I did name the data rows as you had but couldn't put it together. This will replace about 20 charts and gives that little bit of cool factor.

    Thnx,
    Deb

  5. #5
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: use CHOOSE to select different range (Excel 2003)

    Ah that's a good post to keep in the Favorites bins. Problem solved.

    Thnx,
    Deb

  6. #6
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: use CHOOSE to select different range (Excel 2003)

    In looking at your solution further, can you explain why it works?

    1. When I click on the chart to display the series data, it always show "='workbookname.xls'!Rng1" no matter which range is actually selected.
    2. When I looked at the defined ranges, I see your Rng1, Rng2, Rng3 with the OFFSET formulas and so expected to see the chart source data reflect these same ranges but it always shows Rng1.
    3. When I removed the workbook name from the chart source data, it generated an error. Why is the workbook name required (e.g., ='workbookname.xls'!Rng1, not just =Rng1)
    4. As a test, I renamed Rng2 and Rng3 (since they don't appear to be used in the chart source data) but that generated an error so obviously (not to me) they are needed.

    Thnx,
    Deb

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: use CHOOSE to select different range (Excel 2003)

    I can't explain what you describe. When I click on a specific series, the appropriate range is shown in the formula bar, and it is highlighted in the worksheet. See screenshot.

  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: use CHOOSE to select different range (Excel 2003)

    Rng1 is Series 1 on the chart and rng2 and rng 3 are series 2 and 3 respectively. The chart always uses these 3 names, no matter what the selection.

    What the selection does is to change the range that the name refers to (using the offset formula) for example, rng1 is:

    =OFFSET(Sheet1!$B$9:$G$9,3*Sheet1!$B$3-3,0)

    If Michigan is selected then B3 = 1 and the formula becomes:
    =OFFSET(Sheet1!$B$9:$G$9,0,0) which is B9:G9

    If Texas is selected (B3= 2):
    =OFFSET(Sheet1!$B$9:$G$9,3,0) and you get B12:G12

    And if Ohio is selected (B3= 3):
    =OFFSET(Sheet1!$B$9:$G$9,6,0) and you get B15:G15

    [Note: your named ranges are not used 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
  •