Dynamic chart with formua derived data

I am trying to create a dynamic range chart using dynamic ranges, not a table. It's not a problem if data is added to the source data but if I extract data from the source data by formula the chart shows the blank formula cells to contain data and shows nothing. The attached chart explains it better. Thanks for any help.

If I follow correctly, I will ask if you understand how the dynamic named range you are using works - specifically, do you understand how the COUNTA() function fits into the OFFSET() function to determine how many rows of data to include in the named range? As explained in the help file for the COUNTA() function (https://support.office.com/en-us/art...2-53f3219e2509 ), the COUNTA() function counts all non-empty cells, which includes cells with functions that return empty string "". Since an Excel function cannot return a truly empty or blank cell, any cell with a function will be included in the COUNTA() function. the resolution to your problem, I believe, will be to think through what kind of counting function you need here. Perhaps a regular COUNT() function (that does not count the empty text values) on column I or J would be a better choice. Or a COUNTIF() or COUNTIFS() function would better recognize which rows are real data and which are not.

Let us know if you need help formulating a different counting function.

One Named Range needed to be adjusted to take account of the formula blanks in it. I used the column with the persons name. So rather than setting the column height as being th ewhole column minus 1, I used:

=OFFSET(ChartData!$H$4,0,0,SUMPRODUCT(--(LEN(ChartData!$H$4:$H$100)>0)),1)

This bit: SUMPRODUCT(--(LEN(ChartData!$H$4:$H$100)>0))

returns all the cells where the length of the content exceeds 0. In your Named range, any cell with a formula also counted. hence the compressed Y-axis. I then used simpler NR's for the data, e.g.:

=OFFSET(rgNames,0,1) for Data A.

I deleted your total further down the data columns. If you MUST have the totals, put them in a row ABOVE the starting row of the NR, otherwise, everything may well go pear-shaped again.

Oh, I also tidied up (deleted) your unnecessary index columns, and used an array formula to return a unique list of names. Formula used:

=IFERROR(INDEX($B$4:$B$25,MATCH(0,INDEX(COUNTIF($F$3:$F3,$B$4:$B$25),0),0))&"","")

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...