+ Reply to Thread
Results 1 to 4 of 4

How to dynamically update the range for an excel chart

  1. #1
    Registered User
    Join Date
    07-11-2016
    Location
    usa
    MS-Off Ver
    2016
    Posts
    78

    How to dynamically update the range for an excel chart

    Hello,

    I have a Pivot table tab called "Pivot" in which Col A and B are pivot table values coming from tab "Raw Data 1". Column C and D (in red) are not part of the pivot table and are manually added columns which are pulling the data from the tab "Raw Data 2" after vlookup with the pivot table columns (A and B). I have a chart which is built off of Col C and D (in red).


    I have 2 questions:

    1) How will my vlookup formulas in Col C and D drag down dynamically to match the pivot table values in Col A and B when there are more data added to raw data? Do i need to drag it all the way down to an arbitrary row which far enough or is there a formula which will keep adding the vlookups to match the last row of the pivot table?

    2) How can i select a range for this graph which will automatically detect the first and last row for Col C and D as more data gets added to the raw data periodically. How will the chart range automatically adjust itself to include the new additions or subtractions in the raw data. I want my graph to always have a range starting from the first row to the last row of col C and D which will be until the pivot table range in Col A and B. This way i don't need to set a fixed range for the graph which might include 0's or blanks which i want to avoid.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-29-2020
    Location
    NH USA
    MS-Off Ver
    365
    Posts
    2,102

    Re: How to dynamically update the range for an excel chart

    I suggest you convert all your lists into tables and source your data from them. Tables automatically adjust for new data. Let us know if you have questions
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    07-11-2016
    Location
    usa
    MS-Off Ver
    2016
    Posts
    78

    Re: How to dynamically update the range for an excel chart

    Hi

    The formulas got adjusted once i converted to tables. However, my chart range is not adjusting automatically. So basically if i initally have 10 rows and then add upto 100 rows in Col A and B, then Col C and D gets updated with the vlookup. However, my chart is only referencing the initial 10 rows and i have to manually pull it down to adjust to the new rows. How will the chart smartly adjust its data range?

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,424

    Re: How to dynamically update the range for an excel chart

    Similar to maniacb I suggest converting the ranges to tables.
    I also suggest doing the lookups in columns C:D of the table on the Raw Data 1 sheet and building a pivot table from that.
    The formula to populate column C is: =INDEX('Raw Data 2'!C$2:C$7,MATCH(B2,'Raw Data 2'!A$2:A$7,0))
    The formula to populate column D is: =SUMIFS('Raw Data 2'!B$2:B$7,'Raw Data 2'!A$2:A$7,B2,'Raw Data 2'!C$2:C$7,C2)
    Note that when a new row is added to the table on the Raw Data 1 sheet and the pivot table is refreshed, the pivot chart will display accordingly.
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. How to update a chart dynamically when new data is added.
    By ABELNYC in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-21-2020, 04:29 PM
  2. help with vba code to dynamically update the chart and its axis
    By rv02 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-03-2016, 06:08 AM
  3. [SOLVED] dynamically update chart based on cell value
    By Bax in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 11-06-2015, 09:13 AM
  4. Replies: 0
    Last Post: 02-16-2013, 01:39 PM
  5. Dynamically adjust data range to an excel chart
    By rouxbox in forum Excel General
    Replies: 1
    Last Post: 10-11-2012, 04:01 PM
  6. Help to dynamically update Excel chart
    By zad1999 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-13-2010, 08:35 AM
  7. Update chart dynamically
    By Aland2929 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-06-2010, 05:41 AM

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