+ Reply to Thread
Results 1 to 4 of 4

Charts in Workbooks

  1. #1
    Registered User
    Join Date
    06-27-2022
    Location
    US
    MS-Off Ver
    Professional Pro 2016
    Posts
    2

    Charts in Workbooks

    I have a workbook with a worksheet for each month wherein I track electricity usage. Each worksheet has rows for days of the billing period and columns to track things like KWH and to calculate values for cumulative usage, trend, cost per day, cumulative costs per day, etc.

    I have a line graph that uses entries from various columns for various lines. Each month, I have recreated the line graph. I would like to be able to copy it from one month to the next and have the next month chart use that month's values; however, the graph - when pasted in the new month's worksheet - references entries from the previous month.

    Is it possible to copy just the graph-defining variables and them refer to the new worksheet values?"

    Failing that, is there an easier way to edit the Name Manager entries (from Formulas>Name Manager) than manually editing each entry to show the new month's worksheet reference?

    I've attached my workbook to illustrate my question.

    This is my first post on this forum; so if I've violated any rules or practices, please slap my hands and tell me what I've done to offend.
    Attached Files Attached Files

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

    Re: Charts in Workbooks

    Hello J. Kickingowl and Welcome to Excel Forum.
    The closest that I can think of to what you want is (as modeled on the April sheet):
    1. Put all data on one sheet adding a column for the billing month.
    2. Use data validation to produce a drop down that selects the month (cell P4).
    3. Use the following formula to reference the data (see O6:Z37): =IFERROR(INDEX(A$6:A$69,AGGREGATE(15,6,(ROW($A$6:$A$69)-ROW($A$5))/($M$6:$M$69=$P$4),ROWS(O$6:O6))),"")
    4. Change the references in the chart to the new location (i.e. "KWH Used" references 'APR 2022'!$U$6:$U$37)
    Let us know if you have any questions.
    Attached Files Attached Files
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  3. #3
    Registered User
    Join Date
    06-27-2022
    Location
    US
    MS-Off Ver
    Professional Pro 2016
    Posts
    2

    Re: Charts in Workbooks

    Thank you!

    That helps me get out of my conceptual prison and think about the problem differently.

    jk

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,530

    Re: Charts in Workbooks

    If your question has been answered please mark your thread as "Solved" so that members will know by looking at the thread title that your problem is solved. Go to the menu immediately above your first post to the thread and click on Thread Tools. From the dropdown menu select "Mark this thread as solved..."

    If a member helped you solve your problem, consider adding to their reputation by clicking addreputationiconsmall.jpg below their name.

    If you solved your own problem, please provide your solution so that somebody seeing this thread in a search for the same problem will see how it was solved.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

+ 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. Copy grouped charts between workbooks as pictures
    By PaulM100 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-07-2021, 11:10 AM
  2. Replies: 2
    Last Post: 03-29-2013, 06:19 PM
  3. Auto-updating Trend Charts w/ Data from New Workbooks
    By carguru in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 11-15-2011, 11:45 AM
  4. Charts - covering multiple workbooks
    By Bob24601 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 11-27-2009, 01:19 AM
  5. Charts that call data from a variable number of workbooks?
    By Schwavan in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-11-2008, 12:31 PM
  6. Replicating charts between workbooks
    By soonerskies in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 08-24-2007, 09:46 AM
  7. copying workbooks with charts and changing data
    By uriel78 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-10-2005, 10:06 PM

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