The above formula assumes, you are going to control chart display thru cell C2 in the sheet ‘view them here’.A sample formula is below: IF('View them here'!$C$2='Sales',INDIRECT(''Place your charts here'!F11'),IF('View them here'!$C$2='Expenses',INDIRECT(''Place your charts here'!F12'),INDIRECT(''Place your charts here'!f13'))) In the “Refers to:” area we will now write an INDIRECT() spreadsheet formula to refer to one of the 3 cells where charts are placed.You will see a dialog box like this (right): You can define new named ranges from menu > insert > name > define. Now, go back to the sheet where you want to control the display, and define a new named range.
Once the charts are created adjust the width and heights of 3 cells and place one chart in each like above.First, create your charts in a separate worksheet like this (remember you need to create all 3 charts first).The Solution: Use INDIRECT() and a nifty image hack You would rather want to show one chart and let user choose to see the any of the other two, like this: But you don’t want to clutter the project report with all of them. You have made 3 charts to show your company performance in the last 8 years. The problem: I have too many charts & want to show one based on selection Yesterday I have learned this cool excel charting trick and I cant wait to share it with you all.
