Dynamic Chart Works Great until I grab data from different Sheet

JDS21

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

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top