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.
I need to highlight all cells in a column with values of less than 15. However, I need empty cells to NOT be highlighted. Yet, with a simple use of the 'less You have two conditions, so you must declare both of them in condition format. First condition 'equal to '' (double quotes is 'empty string').
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 Guys, Thank for helping. I am trying to create an accounting system. When I select the Product in CELL A1, A2 will show the description, A3 - Price, A4 - Costing.
What I am doing now with EXCEL is create a listdown for A1, and add on a conditional formating for A2, A3, A4. For Example, A1 - Listdown is Apple, iMac, iPhone & iOS. When A1 is Apple, A2-A4 Fonts & Cells filled with White Color.
When I turn A1 to iMac, A2-A4 will change to Orange Font with Black Cell (which can attract my attention). Kind regards Ray. 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 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 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. 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). 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. Apple Footer. This site contains user submitted content, comments and opinions and is for informational purposes only. Apple may provide or recommend responses as a possible solution based on the information provided; every potential issue may involve several factors not detailed in the conversations captured in an electronic forum and Apple can therefore provide no guarantee as to the efficacy of any proposed solutions on the community forums.
Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the.
I am trying to get conditional formatting for a row for when a cell in that row contains a date less than or equal to today (yesterday, today, last week etc) I have tried =IF($W$4,TODAY) which works for if the cell equals today's date, but I cannot figure out how to make it work for if cell is equal to today or less than today. Also, is it possible to now copy this conditional formatting to work for every cell in the column, but only affect the cell's row? Also with this the cell stays hightlighted if it is left blank, is this right? Like =IF(ISBLANK),IF($W$4.