Spy Journal 3.0 Filtering the web for you

I received this question on the website from Eric regarding conditional formatting:

Hi,

I have the same problem as a few people above but I don't think you got the point of the problem,

In xl2003 if you select A3:A9 and enter conditional format =B3 then select any other cell in the range it will show the conditional format for that cell e.g. A7 will show =B7

In xl2007 after following the same steps it will apply the correct formatting but if you select cell A7 to view the conditional format it will show as =B3. You get the same when checking any cell in range A3:A9.

This makes it near impossible to edit or amend or debug conditional formatting once entered.

Any advice would be appreciated.

Eric.

I started to post my answer and realised that i would be better off writing the whole thing as a blog post with some images to explain.

Hi Eric
i understand the problem and the first answer is that you cannot apply the same sort of logic between the two versions. The new versions (including 2010) manage conditional formatting completely differently, though some of the same concepts apply.
First of all your conditional format formula should be =A3=B3. This will apply the conditional format when the cell in A3 contains the same value as the cell in B3, and this will be relative - eg. when A7=B7 the cell in A7 will have the format applied. So you can see that the relative approach is correct in the conditional format manager in Excel 2007/2010
Second the problem you mentioned. When you look at the relative formula in cell B7 in excel 2003 it showed you just that formula. that is actually harder to debug if you think about it in Excel 2003 than in 2007/2010 as there is no way to see what other cells it also applies to. In Excel 2007/2010 the conditional format manager allows you to see the formula - and the range it is applied to and the little range box (circled in the image below) allows you to view this
The manager allows you to confine the selection to be evaluated to the selected cell(s), the current worksheet or other worksheets in the same workbook. If you then understand the relative / absolute nature of the formula you created then you can debug very successfully. I actually preferred this once I got a hang of using the manager to create sets of conditional formats than can be applied to multiple ranges simultaneously.

I just had to write of some of the awesome things that have been happening around here. This blog has been a little empty of late - and that's partly because of the new business taking up time, the photography business kicking along and just the sheer business of life. But some things have been happening that are connected in ways that are amazing giving me cause to write this article. The photo here was taken Sunday afternoon as a planned shoot for a connected human or a cyborg theme. In this case the two people are totally connected and entangled in the cables and trappings of computer hardware. It was a very interesting concept to put together and shoot and the whole team had a great amount of fun doing it.

Lets start with Sunday - the beginning of the week. For just over a year now our church has been searching for a new pastor after the leaving of the last one. Sunday the search ended with the pastor the search committee and the leadership recommended being accepted by the church. At the conclusion of that meeting we had a BBQ lunch and I was able to share with the church first the little miracle that took place where god miraculously provided the two trays of sliced bread we needed for that lunch. I used that as an example to show how Gods care for us in even the little things is an example of his absolute care for us in all things. Stressing and having anxiety over things in our life is wrong, hurtful and unhealthy.

Outlook 2010 has some pretty sweet features. Here are some of the new ones.

Use the Outlook Social Connector with Facebook, LinkedIn, MySpace and Windows Live

Today, we are announcing that you can use the Outlook Social Connector with Facebook and Windows Live. Our partners LinkedIn and MySpace are also releasing updates for their providers. All of the latest providers appear on the provider page.

Download the social connector software

Download each of the social connectors

Choosing the right communication modality with the contact card

There has been lots and lots of rumours, speculation and conjecture on just what the new release of Windows Live was going to contain. The LiveSide website has been the source of plenty of leaked screenshots, links to downloads and other information on the way.

Download links available at LiveSide

Now that is released into the wild, and people are using it I thought I would write up a quick review of some of the new features – and there are plenty.

Live Messenger

• A whole new communicator window, with integration with Facebook and MySpace accounts – means all your contacts, status updates and commenting can be done in one place, along with access to media like photos and videos. Note the Facebook chat feature has not been enabled in the public beta yet but will come later. (Image from LiveSide)
• A new tabbed conversation window – at last!. No longer do you need to have 75 windows open of all your current messaging sessions, they are now contained in one window with tabs at the top. This is a major improvement.

I had a reader request some help with Conditional Formatting in Excel 2007.

Hi

Baffled in trying to get to grips with conditional formating in 2007

Scenario :

Column A = Actual Result
Column B = Target
Column C = Needs to show
1) If Actual result is less than 90% of target turn font colour RED
2) If Actual result is >90% of target and is <100% of target turn font colour AMBER
3) If Actual result is >= 100% of target turn font colour GREEN

If column C meets criteria 1 use character "R", criteria 2 = "A" and criteria 3 = "G"

I can acheive 1 and 3 with =IF(a1b1,"G")) and then conditional format so that if contents of cell = R or G then colour font accordingly </B1,"R",IF(A1>

I`m stumped with the % factor and also think there might be a better way of doing it

Any help would be appreciated :)

Here is a solution to this problem.

We formatted two columns to give two examples as to how it could be done.

The actual column determines the percentage as part of the conditional format and formats in that way.

The result column formats on the “R”, “A”, and “G”.

The formula in Column C1 is =IF(A2/B2<1,IF(A2/B2<0.9,"R","A"),"G") and this can be copied down.

Here are the rules for Column A