+ Reply to Thread
Results 1 to 7 of 7

Auto updating Charts with Dynamically named Ranges

  1. #1
    Registered User
    Join Date
    03-18-2013
    Location
    Australia
    MS-Off Ver
    Excel 2003 - 2007
    Posts
    11

    Auto updating Charts with Dynamically named Ranges

    Hi there,

    Previously I have been able to just peruse through as a guest and help myself out with my problems; however I have finally encountered a problem that I cannot fudge my way through.
    Setting;
    1 2 3 4
    A 30% 35% 45%
    B 12% 13% 45%
    C 22% 32% 55%

    Each week a co-worker will be adding data down columns, (a simple copy and paste), so for column 1, they would have copied and pasted all the data for A,B,C. This is then displayed on an individualised chart, charting the % that A did during week 1, then 2 etc. What I would like to happen is that when they enter the number in column 4 (being the number 4), and pasting a new weeks’ worth of data, the chart auto updates.
    Attempts;
    So far I have tried to have both week and letter set up as dynamically named ranges that are inserted into the chart so each week is updated as they insert data, keeping them from having to change the data source of the chart for each week. *note final version has 52 charts, hence the need for auto updating. However, using the following formula I have only been able to have weeks heading down the rows and A,B & C heading across columns. The table to input data cannot change.
    Formula tested;
    The formula I have tried is; =OFFSET (Sheet1! $A$2, 0, 0, COUNTA (Sheet1! $A$2: A$2) -1).
    I have fiddled around with it so much that this is the base formula I was working with, at one stage I got it so that I could go into the name manager and increase the last digit by 1 and it would add the next column of data into the chart, but I needed this to be automatic.
    If this is possible through the way I have tried so far, excellent, if not I am open to a solution that has a macro button above the table that can be pressed to either increase this last digit by 1 or select the next weeks’ worth of data to be included.
    Thanks.

  2. #2
    Forum Contributor
    Join Date
    09-27-2012
    Location
    Florida
    MS-Off Ver
    Excel XP to 2013
    Posts
    342

    Re: Auto updating Charts with Dynamically named Ranges

    Try this for your dynamic named range you have it locked on row 2

    Please Login or Register  to view this content.
    Tom S.
    ↙ If you find my reply helpful click on the * down there on the left. Yeah that's it, right there, down on the left
    If your question is resolved, mark it SOLVED using the thread tools.

  3. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Auto updating Charts with Dynamically named Ranges

    This might be something of use.

    Convert your original data range to a table. Click in the data range, click on the Insert tab, click on Table, select "My Table Has Headers".

    Select the table and create the chart of your choice.

    When a new column is pasted next to the right-most column, it is included in the table. The chart created by the table is automatically updated.
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

  4. #4
    Registered User
    Join Date
    03-18-2013
    Location
    Australia
    MS-Off Ver
    Excel 2003 - 2007
    Posts
    11

    Re: Auto updating Charts with Dynamically named Ranges

    Tank,

    Thanks for the sugestion, but from the way I currently understand it, by placing $A:$A, the offset function will only count new data entries placed within column A. I however need it to be locked to Row 2, counting along row 2 as new data entries are added.

    Newdoverman, your suggestion works for the problem I posted originally. However, to complecate things now, I need each chart to read from two tables. When it is only reading from 1 it Auto updates fine, but when it is reading from 2 it sits on the original data range and does not update. Solution? This is being picky as I can have 2 charts 1 for each table, but for simplicity and to compare one table with the other on a single chart would be great.

    Thanks.
    Last edited by Xiophoid; 03-18-2013 at 05:41 PM.

  5. #5
    Forum Contributor
    Join Date
    09-27-2012
    Location
    Florida
    MS-Off Ver
    Excel XP to 2013
    Posts
    342

    Re: Auto updating Charts with Dynamically named Ranges

    Sorry, I thought I saw the problem with the formula and posted without reading your full post. To set a dynamic named row you can you can use this formula.

    Please Login or Register  to view this content.
    I also agree with newdoverman that a table is the way to go not just for this reason but for this and many other reasons.

  6. #6
    Registered User
    Join Date
    03-18-2013
    Location
    Australia
    MS-Off Ver
    Excel 2003 - 2007
    Posts
    11

    Re: Auto updating Charts with Dynamically named Ranges

    Thanks for the suggestions and the help.

    a quick goodle search of your latest suggestion Tank helped me find this little gem.

    For those searching in the future the following code dynamically names a range and counts across a row to automatically update a chart.

    Please Login or Register  to view this content.
    Thanks for the help.

    *'rd.

  7. #7
    Forum Contributor
    Join Date
    09-27-2012
    Location
    Florida
    MS-Off Ver
    Excel XP to 2013
    Posts
    342

    Re: Auto updating Charts with Dynamically named Ranges

    You can replace the first CountA formula with a 1 since it just set the height of the range and your count formula also sets it a 1 row high. Glad you found what you needed

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1