How to create an entire line conditioned to the result of a cell?

Here is a pair of tables that fit your description:

The Data table is your original table, with the flight data copied from your sample. The cells in column D are pop-up menu cells, each containing a four item menu: none, RESERVED, CANCELLED, OPENED. Using a pop-up like this ensures that each of the terms will be entered with the same spelling every time, and will be recognized by the formula that controls the triggers for the conditional highlighting rules shown beside the tables.

There are no formulas on the table Data.

AUX is an auxiliary table, used to provide a set of markers to trip one of the rules, causing the colour fill seen in rows 2, 3 and 4 of Data.

Aux contains one formula, entered in cell A2, anf filled right and down to all cells in the body of the table. The error triangles in the bottom row are deliberate—they all flag a reference error caused by this table having one more row than the table above. This table can be maintained to have as many rows as the Data table or can be set to have many more rows, and extended only once in a while when the Data table starts showing similar errors indicating it is now longer than the AUX table.

Formula as entered in AUX::B2

IF(Data::$G2="","",IF(Data::$G2="RESERVED",Data::B2,IF(Data::$G2="CANCELLED",LEFT(Data::B2,LEN(Data::B2)−1),IF(Data::$G2="OPENED",RIGHT(Data::B2,LEN(Data::B2)−1),""))))))

Here's the same formula, stacked to show each part as a separate line:

IF(Data::$G2="","",

IF(Data::$G2="RESERVED",Data::B2,

IF(Data::$G2="CANCELLED",LEFT(Data::B2,LEN(Data::B2)−1),

IF(Data::$G2="OPENED",RIGHT(Data::B2,LEN(Data::B2)−1),

"")))))

Each IF statement, after the first, is called if the comparison in the one before it returned FALSE. If the last one is false, the null string in the bottom row is returned.

As noted, the AUX table may be tucked away in a corner or on a separate sheet. Once running, it needs human access only when it's time to add more rows.

Regards,

Barry

PS: If you are using the current version on Numbers (11.2 or 11.3 at time of writing), you may find the new IFS function makes for a slightly more compact formula.

B.

Here it is using your data. In the interest of shortness, I only included one of the highlighting tables in the screenshot. The other two are titled Canceled and Opened. Technically you only need 2 tables, the third highlighting rule could be the default color you use for the cells in the table (you could make the default gray for "opened"). Nonetheless, I used three highlighting rule tables here.

To make the first highlighting table it might be easiest to start by copying your data table then deleting all the data in the copy. That way you'll have a table with the proper number and width of columns and a bunch of rows to start with. Then add a whole bunch of new rows to the bottom.

I used the highlighting word as the table name and also put it in cell A1

Formula in cell A2 of the highlighting table is =IF(Table 1::$G2=$A$1,Table 1::A2," ")

Fill right then fill all columns down to complete the table.

Copy/paste the table twice.

Change the table titles and the word in cell A1.

In your data table, the highlighting rule in cell A2 would be as follows

Copy Style then Paste Style to the rest of the cells in the table (other than the header row).

To keep everything neat in the end,

1. Create three new sheets,
2. Delete the default table that comes with them,
3. Move the highlighting tables to those sheets. Select the table, Cut, then Paste into the new sheet to move one.

EDIT: There is always an improvement to be made. If you read to the end before starting, it might be even simpler if you started by creating a new sheet and pasting the copy of your data table to it to begin the first highlighting table. After that highlighting table is complete you can duplicate the sheet twice to create the others.

Numbers does not make it easy to do that. It can be done but it takes some work. It is easy to do cells in a single column but not multiple columns or the entire row based on the contents of one column.

In a recent thread I mentioned one way, which is creating a "highlighting" table that has the highlighting rules (and some formulas and a lot of changes to borders, text opacity, etc). You slide it under your data table and it shines through, providing the highlighting you want. But it is difficult to get them to align exactly and, if you are editing the data table, the two tables can get misaligned. It rarely looks completely lined up.

Another way would be to use an AppleScript. You would have to run the script to update all the highlighting; it would not do it automatically.

There is another method that also requires another table. It is only practical for a single highlighting rule or a small number of columns with a small number of highlighting rules. Here is an example with three rules.

The highlighting rule in cell A2 of Table 1 is

You have to make Table 1-1 first, obviously.

To get it into the rest of the cells, select this cell, Copy Style, select the rest of the table, Paste Style.

Table 1-1 is created like this

Row 1 of each column is typed in. For each highlighting rule and each column of Table 1 it needs a column in this table. If there was only one highlighting rule, it would only need three columns. We have three rules in this example so we need three sets of three columns. You can see how this can add up quickly.

Cell A2 =IF(Table 1::$A2=A$1,Table 1::$A2," ")

Cell B2 =IF(Table 1::$A2=B$1,Table 1::$B2," ")

Cell C2 =IF(Table 1::$A2=C$1,Table 1::$C2," ")

Copy/Paste or fill these formulas to the last row of the table.

Select all three columns of these formulas, Copy, then Paste to each of the the other sets of three columns.

I have to add the caveat that is highly likely there is an easier way to do this.

Here's a link to the conversation that included a script solution for a similar question. SG's contribution starts at the bottom of Page 2. There's more on Page 3.

https://discussions.apple.com/thread/8304672?answerId=33110614022&page=2

Regards,

Barry

A simple Javascript for Applications (JXA) script to do that could be as follows:

const statuscol = "G"; // change to column where you record status

const app=Application("Numbers"),
      doc=app.documents[0],
	  sheet=doc.activeSheet,
	  table=sheet.tables[0];
	 
let status = table.columns[statuscol].cells.value();

// permissable color names - "red", "green", "blue", black", "white", 
                              "gray", "yellow","blue","purple"

for (let i in table.rows) {
       if (status[i]=="RESERVED") {
            table.rows[i].backgroundColor="orange"; 
	        table.rows[i].textColor="black";
	   } else if (status[i]=="CANCELLED") {
            table.rows[i].backgroundColor="gray";
	        table.rows[i].textColor="white";
	   } else if(status[i]=="OPENED") {
	        table.rows[i].backgroundColor="yellow";
	        table.rows[i].textColor="black"
       }
}

1. Copy-Paste into Script Editor (in Applications > Utilities). Set upper left dropdown to 'JavaScript'.
2. (First time only) make sure Script Editor is listed and checked at Security & Privacy > Privacy > Accessibility.
3. With the Numbers document open to the relevant sheet click the <run> button.

This can be placed in the menu and/or attached to a keyboard shortcut for frequent updating.

Result with colors as set above (simply edit in script to change these colors to taste):

No changes needed if columns or rows are added except may need to revise the column letter in the script for the Status column.

SG

Hi Ariane,

Are you referring to determining the content of each cell in row, depending on the content (entered or the result of a formula) in one cell in that row,

OR

are you referring to setting the format of each cell (colour of the text or colour of the background) of all cells in the row depending on the result of a formula in a specific cell in that row?

Can you provide, or describe a specific example?

Regards,

Barry

Hi Barry,

You Legend! As always, you have taught me something new. I would never have thought of Text is, Text starts with, Text ends with. And all done with one formula and one set of Conditional Highlighting rules that fill down and fill right!

Regards,

Ian.

Ian,

If only it could be done with one table! ... Or without the need to keep the dimensions of the two tables and the references between them exactly in sync.

SG

You'll want to use Conditional highlighting rules to do that. Are the results in the trigger cell numbers or text?

Can you provide some specifics?

How many colours do you hope to use?

What data in the 'results' cell will determine which colour to fill the row?

What type of data will be in the cells which are to be filled?

Conditional highlighting rules compare the value in the cell to be highlighted with either a fixed value written into the rule or to a value in another cell. So the questions above are to try to determine a way to create a 'partner cell for each cell in the row that is to be highlighted.

A second method is to create a second, single column table with rows that exactly match the height(s) of rows on the main table. The second table would simply copy the result from the cell whose result is to control the highlighting.

The highlighting rule would then be written to act on the values in the second table, using custom fill and text colour settings that fill the cell and set the colour of text in the cell to the same colour.

With the CH rules operating correctly on the second table, that table is then moved toward the back, and slid behind the main table, where it's colours show through the 'unfilled' transparency of the main table's cells.

This method works well with a table not subject to changing numbers of rows or columns, but not as well with tables that are often moved or altered.

Regards,

Barry

PS: SGIII posted a method using AppleScript in reply to a similar question here recently. SG may post a reply to this question as well. A script requires user action to trigger the script each time you want to change the colours being applied, but doesn't require understanding of the script language to make it go (once the script has been written).

That method could provide a more versatile solution.

B.

[EDIT]

PPS: I see I'm suggesting the same set of solution possibilities as Badunit, including a mention of SG's script solution. :-)

I recall another twist on the way I outlined. It is a whole lot easier to set it up if you use one table per rule vs all the rules in one table. In my example of three rules, you would have three identical tables the same size as your data table, the only difference being which rule they handle. And, as Barry showed in his method, these tables can have extra rows and/or columns to allow for growth of your data table. They will be full of error triangles, but that's no problem.

The formula in cell A2 of the "Yes" table is

=IF(Table 1::$A2=$A$1,Table 1::A2," ")

Fill to the right and then down to complete the table

Put the word "yes" in cell A1

Copy the table and paste it twice to create the other two tables

Change A1 for the other two tables.

Set up the highlighting rules same as before but referencing cell in these tables instead.