Dynamic Chart Works Great until I grab data from different Sheet


New Member
Jun 19, 2009
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!


Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".


MrExcel MVP
Sep 2, 2009
How come you don't have the named ranges refer to the Data on Sheet2?


New Member
Jun 19, 2009
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.

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...