Dynamic Ranges/Automatically Updating Charts

    I am at a loss as to how I can automate my spreadsheet to update the chart each time I enter new data. I've attached a portion of my workbook as an example. Ideally I would like to specify a start and end date.


    I have tried offset and match functions but never have accurate results. I am at my wits end and deleted all the dynamic ranges I started. I need a fresh start with this project. Can anyone help me get on the right track? Thanks in advance for any advice or support you can offer.
    Cheers!

    Adding the match function to the named ranges


    Thanks for the speedy reply. For future reference, how would you add the match formula to the defined ranges? This where I ran into trouble previously. I am amazed you did this so quickly...if you only knew how long I have been working on it without successful results. Again, thank you so much.

    Here's the revised named range for ChtLabels with the MATCH function included.


    I have split it over lines so its easier to read in the post

    Code
    =OFFSET(Current!$A$1,
0,
MATCH(Current!$A$12,Current!$B$1:Current!$AV$1,0),
1,
MATCH(Current!$A$14,Current!$B$1:Current!$AV$1,0)-
MATCH(Current!$A$12,Current!$B$1:Current!$AV$1,0)
)


    If you want this to handle adding new data in columns AW and onwards then adjust the Match function changing $AV$1 to $IV$1

    [h4]Cheers
    Andy    [/h4]

    Re: Dynamic Ranges/Automatically Updating Charts


    Hi,


    I'm trying to understand how the max and the min y-scale value is set.
    Can someone explane me how it is done, or how I can do that?


    Thanx,
    Fluppe

    Re: Dynamic Ranges/Automatically Updating Charts


    Found the problem,


    Select the y-axis -> format axis -> set the min and the max value axis scale on auto.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login