Formatting problems

[Tamarad]

I have tried to format a set of data with the following rules: =AND(OR(P2="AIR";P2="SEA");W2="";M2<(TODAY()+7))

If I put it in as a normal formula, the outcome is correct. However, if I put the same formula in as a format (to color the ones that are TRUE) it will only apply to some of the cells the normal formula stated as TRUE.

I have got no idea what I am doing wrong. It seems to me it is the same formula and thus should have the same outcome?

 

[ace19852]

Hi and welcome to the board the rule looks good to me except

All the ";" semi-colon should be "," commas. I'm assuming it's a typo in the post as the conditional formatting panel would throw up an error if tried to put it in!

By the way if there is no date in M2 it's TRUE the conditional formatting will apply.

The other thing I have found in the past is to recheck the formula is as you originally typed it in to the panel as sometimes excel has a habit of inserting extra "".

That's all I can think of and I tried it out on my test file and it works.

If you want to put up a link to your file with sendspace.com or dropbox or any other file uploader feel free and then I'll look at the real data to see if I can spot the problem.

Cheers

 

[Caribeiro77]

Tamarad,
Depending on how your data is set it up you may need to lock some cell references...

 

[ace19852]

Good point Cariberio77. From the dumby file I setup at least P2 should be $P$2 and maybe M2 date although that be required over a range and so doesn't need locking or making absolute.

Cheers

 

[Tamarad]

Thanks for the feedback ace and Cariberio77

If I put commas instead of semicolons, Excel will not recognize it as a separation between parts.
M2 will never be empty since than the data will not be in this file.
I copied/pasted the formula, so there can't be a typo. I have checked this, and I can not see any.
About the locking of cell references: I inserted this formula in the first empty row and dragged it down, since there is a different M2 and W2 on every row that needs to be compared. Locking it will not do any good I assume?

I don't think the problem is in the formula, because when I used it as a normal formula it gave the right outcome. If I put in the same exact formula as a formatting rule, it will not give the same outcome.

I have put the information in a file which I can send with sendspace.com Both the normal formula and the formatting rule are in column AA so the different outcome is visible. Where do I need to send it to?

 

[ace19852]

Put the link on a post here if you look at the icon above it the 6th from the right or the 1 with next to the smilely i.e. the world with a sideways 8.

Cheers

 

[Tamarad]

This should be it: https://www.sendspace.com/file/yvc8vu

 

[ace19852]

Ok I can see the problem and first your formula is with comma in my excel so maybe it's a querk of your keyboard settings I really don't know on that 1

Anyway the problem is you set the range as $AA:$AA which means you want is to start in AA1 but your formula is AA2 etc so you hi-light 1 row too high.

To fix this you could either delete the old rule then click in AA1 put the formula in and specify $AA:$AA or $AA2:AA6094 or greater if you use the rule as it is now !

Cheers

 

[Tamarad]

Maybe ";" is the Dutch version? I've got no idea, but it works

And so does the formatting. Thank you very much for your time and effort!

 

[Caribeiro77]

I think that "," to separate function arguments are only used in UK and US
In the rest of the world( at least for what I see arround this forum) we use ";" to separate function arguments.