Dynamic Chart Works Great until I grab data from different Sheet
Results 1 to 3 of 3

Thread: Dynamic Chart Works Great until I grab data from different Sheet
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2009
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Dynamic Chart Works Great until I grab data from different Sheet

    Hi All,
    My simple dynamic chart works great when using the original data located on the same Sheet (let's say this is Sheet1). I'm Naming my columns of data using the Name Manager along with OFFSET in the Refers to field. I have up to 13 tasks in column A, then Start and End Dates in columns C and D. If I only have six tasks listed in column A, I only get six bars on my bar chart. If I add a 7th or remove the 6th task, the chart dynamically adjusts to show me 7 or 5 tasks. All good up to this point.

    If I Copy Sheet1, I now have Sheet2 with the same data (but then I delete the chart on Sheet2 for this demonstration). On Sheet1 I grab the data from Sheet2 and want to chart it using the chart on Sheet1 (I also deleted the original data on Sheet1, because I'm now bringing it in to Sheet1 from Sheet2). I make sure my Named ranges reflect my data on Sheet1, not Sheet2. I In this scenario, my "dynamic" chart now displays all 13 rows for 13 tasks, even if I only have 6 tasks listed in the data on Sheet2. The chart shows 13 rows of tasks because apparently there are data in rows 7 through 13, even though I entered nothing in those same rows on Sheet2. What was a blank cell on Sheet2 now becomes nonblank on Sheet1. I've tried error functions or simple IF functions to replace what is supposed to be blank cells with "", or #N/A or NA() and all 13 rows continue to get charted. If I simply highlight and delete the data from rows 7 through 13 on Sheet1, the chart updates showing only six rows of tasks, which is correct. That hidden or blank data is Countable too. I've tried COUNTA, COUNTIF and both produce a count of 13.

    What's the solution to make my Sheet1 chart dynamic again using data from Sheet2?

    All constructive suggestions are greatly appreciated!

    Thanks...Jim.

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    16,043
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    7 Thread(s)

    Default Re: Dynamic Chart Works Great until I grab data from different Sheet

    How come you don't have the named ranges refer to the Data on Sheet2?
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    How to post your vba code
    [CODE]your VBA code here[/CODE]
    The # button in the forum's editor will apply CODE tags around your selected text.

  3. #3
    New Member
    Join Date
    Jun 2009
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Dynamic Chart Works Great until I grab data from different Sheet

    Hi AlphaFrog,
    I want to bring the data from Sheet2 to Sheet1 where I will eventually RANK the Start Dates. I'm using Sheet1 as my manipulation sheet. I guess I could do everything on a single sheet, but later I might have multiple sheets with data I want to manipulate and then dynamically chart.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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