Conditional Formatting on other Cell.

Hi Ray,

As Ian states, conditional formatting rules compare the contents of the cell to be formatted to the value in another cell or to a fixed value.

But it is possible to create the effect you want with a little creative thought. Here are two views of the same table. The numbers shown are generated using RANDBETWEEN and a range of 5 to 95.

Visually, each of the examples appears to be a single table. In reality, though two tables are used; one to hold the data, the other to take care of the conditional formatting. The two are stacked, one in front of the other, and the front one (the main data table) is made transparent.

The formatting table—a single row of seven cells—contains a formula that copies the value from the cell in row 1 of it's corresponding column on the Main table. The default cell fill and text colour on this table are set to white, and a conditional format rule, Greater than 50 , is used to change both the fill nd text colours to the same yellow shade when the condition is met.

Below is a view of the two tables side by side. In this screen shot, the text colour on the formattng table has been set to a light grey, and the conditional formatting rule has been set to change only the fill colour.

After resetting the text colour and conditional text colour to white and yellow respectively, the formatting table was selected, then slid under the Main table for the visual result above.

Regards,

Barry

Hi Ray,

That move of the goalposts complicates the problem slightly.

The solution I previously offered can be modified to accomodate the change in the contents of A1, and can change the apparent background colour of A2, A3 and A4, but changing the colour (or other attribute) of the font presents two difficullties:

• Although the "none" choice for font colour is offered, it is apparently not able to be applied to a font either as the default format or as a conditional format. This prevents making the main cell (and its contents) transparent, allowing content in the cell behind it to be displayed. Any formatting of the type must take place in the main cell.
• Conditional formatting of a cell depends on a comparison of the contents of the cell to be formatted with either a fixed value or with the contents of another cell. In the example given earlier (and repeated here) the 'cell to be formatted' is separate from and independent of the 'cell to appear as formatted'. A change in fil colour will be seen through the transparent cell in front of this one, but changes in text colour will not be visible through the text in the cell in front (if identical), or will be seen in addition to (and underlapping) the text in the main cell. (Text in thie formatted cell can't be identical to that in the main cell, as this offers no means of comparison with the control cell.)

Here's what can be done, eploded view first. Main table on the left, then formatting table, then a small table to contain the list of items in the pop-up menu cells in row 1:

In use, the Main table would appear this way. The List table may be moved to another sheet, or otherwise hidden or may be eliminated by rewriting the formula setting values in the Format table as a nested IF statement.

(Each column on this table represents a separate instance of the single column table you described.)

Is the data in A2, A3 and A4 entered data, or is it geterated (or retrieved) by formula?

Is that data used in further calculations, or are these cells the final dispaly result?

A more detailed problem description may make a better solution possible.

Regards,

Barry

Hi iRaychan,

A cell can have conditional formatting that compares its *own* value with another cell, (equal to, greater than, less than that other cell). It can not have a conditional format that depends only upon the value in another cell.

What are you trying to do? Maybe there is another way to tackle this problem.

Regards,

Ian.

Hi Nico,

If you are using Numbers 3.0.1 click on the cell where you want the Conditional Highlighting. Format Panel > Cell > Conditional Highlighting > Add a Rule...

Then click on this

to see this

And either type a cell reference or click on a cell

Regards,

Ian.

Hi Nico and Ray,

A difference between conditional format/highlighting in Numbers 2 and Numbers 3.

Numbers 2 has an absolute reference to the cell first chosen as the comparison value.

Table 1 is Nico's previous year. Table 2 is the next year. Set a conditional format in B2 to compare with B2 of Table 1 and the reference stays at B2 when filling down. Each cell's rule must be then edited individually.

Numbers 3 allows a relative reference.

Click on the reference in the rule and untick Preserve Row and Preserve column. The Rule can be filled down or right and will be relative. Here is the rule for B5 in the second Table:

Unfortunately, this does not survive Export to Numbers '09 (Numbers 2). All rules revert to B2. Bummer!

Regards,

Ian.

Hi Adam,

You need a cell in which you can place a value that can be compared with the value in A1. The cell can be on the same table or on a separate table, or the row or column containing that cell may be hidden, if that is convenient to your situation.

You need a formula in that cell which makes the value in that cell equal to the value in A1 when "B1 contains anything at all" and not equal to A1 when B1 contains 'nothing.'

Use conditional formatting on cell A1 to set its fill to Green when t's value is equal to the value in 'that cell.'

Example: Formatting is done on the table named Main. 'That cell' is Test::A1.

Formula:

Test::A1: =IF(LEN(Main :: B1)<1,"not "&Main :: A1,Main :: A1)

A1 does need content of some sort. The content can be hidden by setting the text colour for the cell to match the background colour (in both 'normal' and conditional formats).

Regards,

Barry

Hi Adam,

The content of A1 may be irrelevant to to your table, but it is crucial to the application of conditional formatting to cell A1.

What you want is that A1 will be filled with green if B1 contains any data.

A1's fill colour can be changed by applying a Conditional format rule to that cell (A1).

Conditional format rules compare the contents of the cell to be formatted with either a fixed value or with the contents of another cell. In your case, you cannot use a fixed value, as you want the format applied to A1 to depend on the value entered in a second cell.

As you had not provided any information concerninb the content of A1, I provided a solution that did not depend on specific content (or lack of content) in A1. The solution does exactly what you want—it sets the colour both the background fill and of the text displayed in A1 to green when B1 contains any data.

With the new information (cell A1 may be empty), a simpler solution is possible. The simpler solution, though, requires cell A1 to remain empty—something that is difficult to guarantee if the document has more than one user, or if any user forgets that requirement.

The selected table is Main. Main-1 is a duplicate of that table, with the same CF rule applied to cell A1. Content has been added to B2 to trigger the rule. Cell A1 must remain empty to prevent the green fill being applied when B1 is empty. (To me, that makes the earlier solution the more stable, and thus more desirable) one.

Regards,

Barry

OK, I've not been very clear about this, so here is a screenshot of what I've got and what I need it to do.

We give out locker keys, and mark them on the sheet, alongside their name (irrelevant). And we check them in and out, as per the IN OUT columns.

If someone has checked in, their key number stays normal, normal text, normal background.

When they check out, ie. when there is a value (time) in column D, we then want the key number box (column A) to change to a different background color, as per the above snapshot.

So the value in A is a number (from 1-45) and the value in D is a time value (actually a date and time value with the date element hidden).

Maybe this makes it clearer, and will help you come up with a solution that works for what I need.

Sorry about the previous posts, I didn't think I was being vague, but clearly I was.

Adam.

"Thanks again, but I'm really looking to change toe colour of A based on the contents of D"

Two views of the same table are shown below. The first shows the state of the table at 11:30 AM, the second the state of the same table at 3:00 PM on the same day.

Disregarding my choice of colour, does this fit what you are asking?

Regards,

Barry

Hi Yellowbox,

this is exactly what I'd need but cant find out how to do it.

In my numbers table I keep tracks of all the visits to a website month by month, year after year. I'd like a cell to turn red if the result in month X is lower than that in month X of the previous year...

So I really need to compare the value in that cell with another one (and of course not against a fixed value)

Can you help?

Thanks,

nico

Thanks Ian for the swift reply.

I managed that and I was hoping to hear about a 'multiple' selection? So that if I select the whole set of cell for 2013 I can 'compare' them all at one with the above cell from 2012... any chance to do that?

I am using Numbers 2.3 (am refusing to upgrade to maverick and the new iWork set after the reviews I've read)

Cheers mate!

...the idea is to set the rule for the whole table, so that every time a value is added, also in the coming years, the rule will be applied without having to manually add one to each single cell.

Hi Wayne,

am not upgrading as am reading of too many disasters... wacom tablet no longer working and so on.

You are talking to someone who's been using apple since 1984... a real evangelist who's been 'converting' many for many years. BUT... I really feel they are Micosoftizing themselves and am getting more and more fed up with their products, softwares and policies!

cheers.

nico

I have a similar but slightly different problem to solve.

I need cell A1 to go green if cell B1 contains "anything at all". B1 contains the time, in 09:00 format but is otherwise empty.

When the time is inswerted into B1, I need A1 to go green.

Anyone??

Thanks.

Adam.