Macro removes duplicates but Charts blanks - Help needed

Hi,

I am new to the forum and although not new to Excel haven't used it in depth before. Anyhow, what started as a small work project has become a bit of an obsession, I have one small and nagging problem to solve.
I have setup a template that produces 12 charts when Data is pasted into worksheet1. some of the charts are formula driven and some are Macro driven. I want the whole process of producing the charts to be as automated as possible.

The one chart I am stuck with takes the Data (a list of names) from Row E of worksheet1 and copies it into Column A of worksheet2 (can range from 5 names to 1000) . column B of the same worksheet then looks at Column A and counts how many times each name appears. So I then have a list of names and how many times each name appears. I then have a Macro that removes the duplicates of the names and only shows unique Data and displays the data in a table - so far this is all okay.

The problem lies with the chart , I set the chart up by inserting a chart that took all the data from column A2 to A1000 - to allow for the max amount of data possible .After the duplicates are removed the chart is plotting the Blanks (it's a bar chart) . I don't have any option in the table to remove blanks. I am sure there is formula or a way of doing this , at the moment Iam playing with the idea of if the cell is empty inputting NA into the cell and then hopefully can remove this via the table but I am stuck with the Formula.

Any help would be greatly appreciated as i have been stuck on it for two weeks now. Apologies for long winded write up and hope I have put this in the correct forum.

Thanks

I think I may be recording the macro in the wrong order, is there a way once I have recorded the macro to look at the code and only plot the chart to the end of the String?

Giving this a bump in case any one can help me get this resolved for Monday.

Any ideaS? Must be a way of not plotting blank cells in excel 2010.

Hi Jeff,

Thanks for the information, this could well be the answer I am looking for, just reading up about it now and will give it a shot in the next couple of hours, Thanks very much for the help. i will report my progress,


cheers

Seem to be getting somewhere with the Dynamic Charts , apologies again for long winded explanation but Were I have got too

column a = 'name'
column b = 'calls'
define name 'name' =OFFSET(Sheet1!$A$1,1,0,COUNTA(Sheet1!$A:$A)-1,1)
define name 'calls' =OFFSET(Sheet1!$B$1,1,0,COUNTA(Sheet1!$B:$B)-1,1)
i can get he chart to update dynamically with the calls but it does not display the name on the 'x' axis.


i think this will work as tested it with the formulas in place in my Cells A2:A1000 and it plots the data accordingly accept it doesn't dislay the name.

Hi Jeff,

This works perfectly, I have put my formulas in place and all seems to work well.

As i am knew to the Dynamic charts I would like to check I have the correct way that you obtained this result, is there any chance you could give me a brief summary of how you achieved it, I tried to replicate the reuslt but as mentioned above don't seem to be getting the month on the x axis although it will plot the total.

many many thanks

Dave

Thanks again Jeff,

i am going to give this a go tonight and will post back.

Cheers

Dave

seem to be getting this message when trying editing the series and adding this to the after field :

a formula n this worksheet contains one or more invalid references

verify that yout formulas contain a valid path, workboook, range name and cell reference.

have tried it several times but get the same message.

got it , don;t kow how i messed up firs tfew time but got there now, how do i mark as resolved and star?


thanks