Dynamic Charting Defined Names Issues

Hello!

I am working on a spreadsheet in which I have a dynamic chart that changes when the user checks a box (form control). The check boxes turn off and on certain columns in the chart. I used the "Define Name" feature and created the various ranges using the Offset function and CountIf function (I have formulas in the blank cells).
Everything works correctly except that when columns are unchecked (thus removed from the dynamic table), the named ranges do not recognize the column that is now completely blank. This results in a "Invalid Reference" error message every time a box is unchecked and the defined range turns to all blank cells.

Does anyone know what will fix this? Possibly a change in the "Refers to" formula under the define name feature?

If that is too confusing I can attempt to clarify further.

Thanks for your help in advance!

Drew

Andy,
Thanks for responding!

That makes sense that I need a valid reference. The problem is that when the box is checked, all references are valid, however, when my check boxes are not checked, the reference turns invalid.

I am not sure if I understand your suggestion to use a single cell. Could you elaborate?

Thanks,

Drew

The problem with this is that I have 15 columns and the check boxes turn off single columns. When a box is unchecked I do not want that column to appear on my chart at all.

Okay, that makes sense. Would I accomplish this by creating a macro to hide the columns that are unchecked?

Thanks, you have provided much help!

Okay, could you give me an example of this macro? I had some luck with getting it to work on the same sheet, but not over multiple sheets
I need the check box on Sheet1 to hide column I:I on Sheet2.
Thanks!

Thanks, I was able to get it working. You have been a large help. Thanks!