Chart presenting/plotting data for blank cells

Question

Hi everyone

I am creating and fine tuning an XLSM which is intended to automate a process our company has been using for a while. The workbook starts with a User Form in which the user selects two files with data and then excel will import the data, compare it and create a report with tables and a radar chart.

The files which the user selects store sensor calibrations for a caliper. there are up to 60 sensors on each caliper and between 1 and 6 calibration points created.

If the caliper has 60 sensors and is calibrated at 6 points, then Excel populates 6 summary tables and a single Radar Chart which has 12 data series (each series having 60 data points).

Where I find issue is where there are less than 6 calibration points being compared. I have conditional formatting and formulas written to give "" values (=IF(ReferenceCell="","",ReferenceCell) and hide empty tables but for some reason the radar chart still presents (plots) points for empty cells and the legend still shows the line details.

The snip below shows the tables being created on a tool where there are 3 calibration points:

If there were 4 or more calibration points, then there would be more tables visible to the right of the page break (columns Y, AA, AB.....).

Below is the chart created from the very same data:

You will see that, even though all the values in series 7 to 12 are "", the chart still shows it (you will notice that the radar axis goes from 900 to 2600 and the data from series 7 to 12 have been given a zero value so are plotted 900 below the centre) and the Legend still presents the Line types for series 7 to 12.

Is there some way in which I can have this fixed so that only series which have data is presented? I can handle a little VBA if this is the best option. Because this will be used by a number of users, I want to make everything as automated as possible so we don't have to go through the process of unchecking series in the chart "Filter" panel?

Any suggestions are welcome with much thanks.

Kind regards

Derek

Answer 1

Could you share us a test file without sensitive information in it?

For sharing a sample workbook, upload to OneDrive or any other cloud drives. Then post a link here.

Hi Snow Lu

Thanks for the reply. I was trying to work out how I could accommodate your request without providing sensitive information and I managed to work out a solution using VBA.

I used a function to determine which of the 12 data series would be filtered:

=IF($B$1<F1,TRUE,FALSE)

Then I used the following VBA code to look up values in varying reference cells:
Worksheets("Sheet1").ChartObjects("Chart 1").Chart.FullSeriesCollection(1).IsFiltered = Worksheets("Sheet2").Range("A1").Value

Works like a treat.

Thanks very much again.

Derek

Answer 2

Could you share us a test file without sensitive information in it?

For sharing a sample workbook, upload to OneDrive or any other cloud drives. Then post a link here.