• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditionally formatted arrows within a shape

Vivek D

Member
I've created a dashboard as shown below. The boxes at the top are merged cells and the tables below are pivot tables.

I'd like to have the boxes part done using shapes though as that will give me the flexibility to move them around, change their size, make them all the same size etc without impacting the tables below.

I know how to do most of it except the arrows. How can I get the conditional arrows to show up with conditional coloring just the way conditional formatting works?

What's the easiest (if possible non-VBA) way to achieve that?

Conditional Arrows in Shapes.png
 
Hi Vivek ,

Isn't it just a matter of having 2 rules , one for the arrows , and the other for the cell coloring ?

Narayan
 
By box I am referring to the Revenue, GM and OM boxes up top. I want to be able to make that entire box and it's contents with shapes so that I can move them around without being tied to cells.

Conditional Arrows in Shapes.png
 
You can accomplish it by using Camera command tool.

To add Camera, go to Quick Access Tool Bar and hit the arrow to the right. Choose More Commands and find "Commands Not in the Ribbon" and choose "Camera".

Select range of cells you want, and hit the newly added Camera button. Select where you want to place it. It will by dynamic and will show all formatting you have done in the range you chose.
 
With VBA, I guess I'd have to have several shapes pre-defined (one set for each place it needs to be shown) and then through code show/hide them right?
 
You can accomplish it by using Camera command tool.

To add Camera, go to Quick Access Tool Bar and hit the arrow to the right. Choose More Commands and find "Commands Not in the Ribbon" and choose "Camera".

Select range of cells you want, and hit the newly added Camera button. Select where you want to place it. It will by dynamic and will show all formatting you have done in the range you chose.

Wow... That seems a really interesting functionality. Never heard of that. Thanks.
 
Basically the camera tool takes a snapshot of a range, so you could do your calculations in a hidden sheet & add the arrows in a separate cell & take a pic of that & paste onto your shape & group
 
You are trying to apply CF to a merged cell, how does Excel know which one of the merged cells you are working with?

Avoid merging cells


Merged cells can help you arrange values in a meaningful way, but they come with problems -- numerous problems, big problems.


For instance, Excel won't apply column formats to a merged cell unless you select all the columns that comprise the merge.


In addition, not all cell formats, stick once you emerge a cell.


You can't sort a column with merged cells.


You can't even select a single-column range if there's a merged cell in it -- go ahead, try!, the whole column will become merged, not good.


You cannot put a filter on it. The problem is the filter is completely useless because the filter will groan with the "merged cells need to be identically sized." Warning, which in English means you have to make each group of merged cells the same size as the largest group. And you have to find them all!


Merging cells in columns and rows could lead to data loss, bad thing.


Formulas and Functions that refer to merged cells will not work, bad thing.


Don't hesitate to use merged cells if you really need them (you don’t), but they will limit what you can do to the cells and even the columns involved.


Center Across Selection is a far better alternative to merging.


To apply this format, select the cells you want to appear merged and then launch the Alignment group dialog, Ctrl + 1, and click the Alignment tab. Center Across Selection is in the Horizontal drop-down.


You will get the desired look you want but without the merged cell's problems.
 
Back
Top