[iamnotmad]

Hi all, can I use a cell reference in a conditional format? Does not seem to work unless I am missing something.

In other words fill color blah if cell value is greater than or equal to <B2>. That type of thing.

[krove]

It works if you are formatting the same cell that contains the numbers you are evaluating. From what I gather, you want to conditionally format a specific cell based on the value of another cell. That is not possible from my testing.

[turtle777]

Yes, you can.



You need to set the COndition to "Formula is" and then you can use any function to create whatever condition you like.
Even multiple nested if...then... conditions are possible.

Works with relative and absolute cell references.

-t

[iamnotmad]

Whoa, thats awesome, however, WTF? That window looks different than mine. For one thing mine is titled "Conditional Format" not "Conditional Formatting" like yours. But more importantly, I have no "Formula Is" condition!

Also, I do not have add/delete buttons, I have +/- round buttons, and there are other window differences. I am using Numbers 1.02 which I thought was the latest?

Please explain!
Thanks!

[turtle777]

Oh, woah, Numbers might not be able to do it.

I misunderstood "Numbers" in your title to mean numbers generically ("conditional formatting of numbers"), rather than the application.

I am using M$ Excel. This screenshot is from the Office 2004 for Mac version.
I'll have to check when I come home if Apple Numbers can do that.

-t

[iamnotmad]

ok, yeah thanks, I just figured that out too, that it was excel you posted. Thanks anyway.

I did find a workaround for this here: Work around for lack of referenced conditional formatting in Numbers.

The only problem with the work around is that it uses an intermediate table, but I guess it could be worse - could not be able to do it at all!

I imagine this feature will show up next major numbers update.

[iamnotmad]

oooh I just figured out a much better workaround.

modified the formula in the cell that I wanted the conditional on to add a space in front of the value if greater than a referenced cells value. Then I used the conditional format rules to check "if starts with" a space then format like blah.

Since I am using currency, I included the $ sign and had to reformat if it goes into the other value, but it all works well now! MUCH better than the thing I just posted above - no extra table or even cell. It just complicates the formula a little with a if.

Here is my original formula:

Code:
SUMIF(Line Items :: $B4:$B24,F3,Line Items :: $C4:$C24)

Now the formula looks like:

Code:
IF(SUMIF(Line Items :: $B4:$B24,F3,Line Items :: $C4:$C24)>D49," $"&SUMIF(Line Items :: $B4:$B24,F3,Line Items :: $C4:$C24),SUMIF(Line Items :: $B4:$B24,F3,Line Items :: $C4:$C24))

And the conditional has 1 rule:

Text Starts With and a value of " " (a space, no quotes) and I change the highlight color.

Fix the alignment (one time) for when the condition is true and that was it. Works great.

Hope this helps someone!

[turtle777]

Wow, overlaid worksheets ?

Very creative. But I'd shoot myself if I had to use this on a regular basis.

-t

[iamnotmad]

I agree turtle, you'd have to be more than clinically insane to do that other workaround.