Hiding Charts

[twilsonco]

Hey guys, just signed up.

I've a sheet for tracking grades, and there are separate columns for each assignment type and a chart under the column to show the scores of the completed assignments. The columns themselves have conditional formatting such that they become hidden based on some trigger cells that are either 0 or 1. This all works fine, but i'd like the chart below a column to become hidden with the column. I've tried using an image linking trick I found (How to Conditionally Show or Hide Charts - Excel Chart Templates & Tutorials | Chandoo.org - Learn Microsoft Excel Online) but when implemented several times, 98 times in my case, the whole workbook comes to a grinding halt, unusably slow. Is there another way I could have a graph become hidden (or at least blanked out, such that it becomes an empty white space) that doesn't involve macros (this sheet is distributed such that I don't want the users to have to question the security of the file)?

Thanks,

Tim

PS here's links to the file before and after I made the charts autohide with the above trick. to make them hide, enter a number into the "weight" cell for the far-left assignment in one of the course sheets.

before autohiding charts: https://www.dropbox.com/s/qq996d0pmcs1d5n/GRADES template 1,1,2.xlsx

after: https://www.dropbox.com/s/uon87epw1thiqtp/GRADES template 1,1,3 slow chart hiding.xlsx******** type="cosymantecnisbfw" cotype="cs" id="SILOBFWOBJECTID" style="width: 0px; height: 0px; display: block; "></object>******** type="cosymantecnisbfw" cotype="cs" id="SILOBFWOBJECTID" style="width: 0px; height: 0px; display: block;"></object>

 

[Steve=True]

Hi twilsonco,

Welcome to the forums.

Since each of your charts is in a column, and you are hiding the columns that will suppress the chart and data,

i would recommend changing the chart settings so that they move and size with the cells.

Here are the charts before:

Here are the charts after you hide a column:

Here is a a step by step tutorial and video that shows you how to change the size properties for your charts in Excel since this can be tricky to find.
[h=2]How-to Show Excel Chart Data and Keep Chart Size when Hiding Spreadsheet Columns and Rows[/h]Please let me know if this is helpful.

Thanks

Steve=True

 

[twilsonco]

Thanks Steve! Sweet Trick!

However, When I said "column" I didn't mean a single column, but more like 6 columns that make up all the information for a given assignment. Also, when I say "hide", what I really mean is that the space used by the given assignment has conditional formatting to take away the borders and make the text white so you can't see it, thereby "hiding" it from the user to make the sheet look cleaner.

Finally, I need the use of space above the assignments, so actually hiding entire columns is kind of out of the question.

If you look at the sheet I linked to in the original post, you'll see what I mean. Since I'm not hiding columns, I can't use your awesome trick.

Given the layout of the sheets in my workbook, can you think of another way to hide, or "hide" the charts when the assignment above them become "hidden"?

Again, to hide the assignments, enter 100 under "Weight" for assignment type 1 and watch the others disappear, but their (empty and ugly) charts remain.

Thanks,

Tim
******** type="cosymantecnisbfw" cotype="cs" id="SILOBFWOBJECTID" style="width: 0px; height: 0px; display: block;">******** type="cosymantecnisbfw" cotype="cs" id="SILOBFWOBJECTID" style="width: 0px; height: 0px; display: block;"></object>