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.
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.
