Using INDIRECT in the Formula That Defines a Graph's Data Range

  • The project I'm currently working on turns out around 20 sets of results per day. Each set has 10 line graphs and 10 bar graphs associated with it. The number of points on the graph differs with every set of results and I have to re-set the graph's data range for every new set of results


    I'm looking for a way to speed up this process as I typically have to access 400 or so graphs every day and manually change 800 or so entries. Each time I have a new set of results I go to each of the 20 graphs, click either on the line or the bar, and this brings up a formula that reads: =SERIES("'R' Value",'RT-Up'!$A$7:$A$25,'RT-Up'!$D$7:$D$25,1) which shows where the data is coming from. I'm hoping that I can use INDIRECT to replace the two instances of "25" in that formula ($A$25 & $D$25) and place the row number in an external reference cell.


    I only discovered INDIRECT about half an hour ago (by courtesy of Carim in another thread) so I don't really know how to use it properly yet, but everything I've tried so far isn't working. Is this a situation where I can actually use INDIRECT and if so, how do I properly use it?

  • Re: Using INDIRECT in the Formula That Defines a Graph's Data Range


    Hello again,


    Say your external reference cell is G1 ...


    you could test following :


    Code
    1. INDIRECT("'RT-Up'!A7:A"&$G$1)


    and

    Code
    1. INDIRECT("'RT-Up'!D7:D"&$G$1)


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Using INDIRECT in the Formula That Defines a Graph's Data Range


    Aha - you've followed me to this thread too!
    I've entered it as: =SERIES("'R' Value",INDIRECT("'RT-Up'!A7:A"&$G$2),INDIRECT("'RT-Up'!D7:D"&$G$2),1)
    and it's giving me: ! That function isn't valid. OK.

  • Re: Using INDIRECT in the Formula That Defines a Graph's Data Range


    Well ...well ...


    It could very well be that the Graph Series cannot handle the Indirect() function ...:wow:


    Most probably the solution will be feasible with the Offset() function ...


    And even easier would be for you to use named ranges which are dynamic ...


    Do you know how to use these dynamic named ranges ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Using INDIRECT in the Formula That Defines a Graph's Data Range


    The ranges that are in the formula were automatically inserted there by Excel; I didn't write any part of that formula. I just change the row number each time I have a new set of results. That said, I'm happy to try anything, as long as it's clear what I need to do. If we can get it right it will be another great time saver for me as it drives me nuts having to change hundreds of range values every day and I'll happily spend whatever time is needed now to make it work.

  • Re: Using INDIRECT in the Formula That Defines a Graph's Data Range


    To create a Dynamic Named Range - Formula ... the best possible explanation is Debra's ...


    see Dynamic Named Range - Formula


    Once you have created your ranges ... there will be nothing else to do ...:wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Using INDIRECT in the Formula That Defines a Graph's Data Range


    Not too much luck I'm afraid. I created two named ranges as per directions. They both work OK because when I use the SUM function it returns the correct sum of the values with the range. The problem comes when I enter the named ranges in the graph formula; they're simply not accepted. Pressing Enter achieves nothing; the cursor just sits there, blinking at me.

  • Re: Using INDIRECT in the Formula That Defines a Graph's Data Range


    If I recall correctly ... whenever you are using named ranges in a graph formula, you need to go defining it the whole way ...


    'WorkbookName.xlsx'!YourRangeName


    Hope this will help



    P.S. For the sake of references, you can take a look at following
    http://www.databison.com/create-chart-using-named-range-in-excel/

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • Re: Using INDIRECT in the Formula That Defines a Graph's Data Range


    Thanks again Carim. I'll carefully go through that reference and come back if I have further issues.

  • Re: Using INDIRECT in the Formula That Defines a Graph's Data Range


    You are welcome ...:wink:


    Do not hesitate to come back ... should you face any difficulty ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)