Hello!!
I'm building a high level audience dashboard and am setting it up so arrows and circles on the dashboard page change direction and color based on values on a worksheet page. Since I am not good with Macros and can't guarantee the audience will not turn them off when opening the file, I'm doing this without macros.
I have about 250 or so areas on the 5 "front page" tabs that will update when I update the values in the worksheet behind the scenes (same workbook). I've done gthis using two methods: I have used linked images off conditionally formatted cells to create colored circles.
I have also linked blank images to named cell ranges with those ranges corresponding to Index formulas that look up the cell with the correct arrow based on the corresponding data point. This is how I get the arrow color and shape to change as the data changes. The Index formulas are only looking up across 4 rows and 2 columns so not overly complex.
It all looks so pretty and clean BUT the **** thing is super slow to respond to any modification including selection of drop downs.
Example: I created a time period selection drop down (3 choices) that feed the 5 dashboard tabs. When I click on the drop down, the screen seems to freeze, the spinning wheel pops up, and the title of the top contains (Not responding). It may do this for 15-30 seconds before showing me the options and allowing me to select. Once I've selected, the same thing happens before the data updates. No Calculating count up, just a freeze.
Since this dashboard is being made for a high level audience, this kind of a glitchy lag will make my otherwise sleek and cool dashboard seem clunky and unresponsive.
I've run tests showing that if I remove my names and linked images, everything seems to respond instantly so I've tried reducing the number of them. It was originally all index named linked images which I turned into just linked images (arrows to circles) to try and reduce the amount of index calculations by 40% but those either take just as long to run or don't help enough to make a difference.
I need some suggestions (non macro) to try and resolve this or else I'll need to remove all my pretty shapes and colors and make it a boring text dump that's at least functional.
I'll post the file for sharing if necessary but would rather not as the data could be considered proprietary and confidential (not a lawyer, but trying to be cautious)
It seems to me like 250 or so linked objects shouldn't be glitching Excel like this but I'm not advanced enough to understand what's happening under the hood to know how to fix it.
Thanks in advance for advice and thoughts and ask any clarifying questions if my post isn't making sense.
Jayy2121
I'm building a high level audience dashboard and am setting it up so arrows and circles on the dashboard page change direction and color based on values on a worksheet page. Since I am not good with Macros and can't guarantee the audience will not turn them off when opening the file, I'm doing this without macros.
I have about 250 or so areas on the 5 "front page" tabs that will update when I update the values in the worksheet behind the scenes (same workbook). I've done gthis using two methods: I have used linked images off conditionally formatted cells to create colored circles.
I have also linked blank images to named cell ranges with those ranges corresponding to Index formulas that look up the cell with the correct arrow based on the corresponding data point. This is how I get the arrow color and shape to change as the data changes. The Index formulas are only looking up across 4 rows and 2 columns so not overly complex.
It all looks so pretty and clean BUT the **** thing is super slow to respond to any modification including selection of drop downs.
Example: I created a time period selection drop down (3 choices) that feed the 5 dashboard tabs. When I click on the drop down, the screen seems to freeze, the spinning wheel pops up, and the title of the top contains (Not responding). It may do this for 15-30 seconds before showing me the options and allowing me to select. Once I've selected, the same thing happens before the data updates. No Calculating count up, just a freeze.
Since this dashboard is being made for a high level audience, this kind of a glitchy lag will make my otherwise sleek and cool dashboard seem clunky and unresponsive.
I've run tests showing that if I remove my names and linked images, everything seems to respond instantly so I've tried reducing the number of them. It was originally all index named linked images which I turned into just linked images (arrows to circles) to try and reduce the amount of index calculations by 40% but those either take just as long to run or don't help enough to make a difference.
I need some suggestions (non macro) to try and resolve this or else I'll need to remove all my pretty shapes and colors and make it a boring text dump that's at least functional.
I'll post the file for sharing if necessary but would rather not as the data could be considered proprietary and confidential (not a lawyer, but trying to be cautious)
It seems to me like 250 or so linked objects shouldn't be glitching Excel like this but I'm not advanced enough to understand what's happening under the hood to know how to fix it.
Thanks in advance for advice and thoughts and ask any clarifying questions if my post isn't making sense.
Jayy2121
