Solved

conditional formatting in attached file not working in excel 2007

Posted on 2014-01-06
6
268 Views
Last Modified: 2014-01-07
Hi Experts

I cannot get the trending arrows to show in excel 2007...the attached example is excel 2003...my table (see tab 5 attachment) goes across in my workbook, as opposed to downwards as per tab 5.
learn-conditional-formatting-exc.xls
0
Comment
Question by:route217
  • 2
  • 2
  • 2
6 Comments
 
LVL 22

Expert Comment

by:Flyster
ID: 39759892
I have Excel 2007 and the formatting seems to be working. Are you not seeing the arrows? Could you provide your 2007 workbook?

Flyster
0
 

Author Comment

by:route217
ID: 39759915
Hi expert

yes not seeing the arrows and...cannot up load due to restriction. ..sorry.
0
 
LVL 12

Expert Comment

by:Harry Lee
ID: 39759916
route217,

If you don't see the arrows in Excel 2007, your problem is absolutely not conditional formatting, since the trending arrows are shown using In Formula Concatenate by using &.

All your conditional format do in Tab 5 is to give the text color Red, Blue, or Black.

I'm opening your file with Excel 2003, 2007, 2010, and 2013, and I don't have any problem seeing the Arrows.

Can you explain your problem a little more?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:route217
ID: 39759943
Hi experts

I have tried to recreate the trending  arrows using new worksheet (lets ignore the one I uploaded) this is where I cannot see the trending arrows...when i open the attachment its fine. ...the problem is when I try to recreate in a new worksheet....

and apologies if the question was poorly worded.
0
 
LVL 22

Accepted Solution

by:
Flyster earned 250 total points
ID: 39760067
See attached file. It uses the same formula as the one you provided, I just modified the cell reference:

=ROUND(A2,0) & " " &  IF(A1<A2,"¿", IF(A1=A2,"¿","¿"))

The first part, =ROUND(A2,0) , just rounds the number in the reference cell. The second part,
 IF(A1<A2,"¿", IF(A1=A2,"¿","¿")), is an IF Statement that selects which symbol to show. The symbols appear through the formula, not conditional formatting. The formatting comes with the font color being changed depending on the cell value change.
TrendingArrows.xlsx
0
 
LVL 12

Assisted Solution

by:Harry Lee
Harry Lee earned 250 total points
ID: 39760575
Route217,

As Flyster says, it's about the formula that you have issue with.

You have to first understand the formula.

Please be aware that EE is not supporting the Triangle shapes and Round Dot. I have turn them into text so that you don't see the ¿ like in Flyster's post.
The & inside the formula is to help you combining the results of multiple section of a formula, and convert the whole result into a Text String.

In your formula, =ROUND(B5,0) & " " &  IF(B4<B5,"Up Pointing Triangle", IF(B4=B5,"Round Dot","Down Pointing Triangle")), the sections are
=ROUND(B5,0)
&
" "
&
IF(B4<B5,"Up Pointing Triangle", IF(B4=B5,"Round Dot","Down Pointing Triangle"))

While B5 = 549, the first section =Round(B5,0) will return 549. The second section will return " ", A Space.
Then the third section will compare B4 and B5. If B4 is smaller then B5, it will return a Up Pointing Triangle. If B4 = B5, it will return a Round Dot. Finally if B4 is not smaller than B5, and B4 is not equal to B5 (Which means B4 is lager than B5, then it will return a Down Pointing Triangle.

In your sample, B4 is 566, and B5 is 549, means B4 is larger then B5. The 3rd section will return a Down Pointing Triangle.

Combining the result of the 3 sections it will give you "549" & " " & "Down Pointing Triangle".

Combining the use of conditional formatting. If B4 is larger than B5, it will turn the text into Red, It will give you a Red Color result of 549 Down Point Triangle.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

685 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question