How can I hide rows that have a field with a particular entry

The brute force method of doing this would be for the second table to be filled with formulas like

=IF(Table 1::A1 = "", "", Table 1::A1)

which will make a complete copy of the first table. Then use Reorganize (which you'll find on the toolbar) to create a filter to show only rows that have that particular code in whatever column it is in.

Your second Table can be set up to transfer only the rows containing the specific code you are looking for.

One way to do that is to create an Index column on the main table, then use LOOKUP to return rows based on the values in the Index column.

Here's a small example.

The Main table is on the left, the Breakout Table on the right.

The target code in the example is selected from a pop-up menu in cell A1.

Two formulas are used, one in the cells of the Index column on the Main table, the other in all cells below the top row in the Breakout table.

Main:

E2 (and filled down the rest of column E): =IF(A=Breakout :: $A$1,MAX($E$1:E1)+1,"")

Breakout:

A2 (Filled right to column C, Filled down to the bottom of each column):

=IF(ROW()-2<MAX(Main :: $E),LOOKUP(ROW()-1,Main :: $E,Main :: B),"")

Values used in the body cells are sample ones intended to show the original cell address of the content, and the code used on that row.

Dscriptions of the functions used can be found in the iWork Formulas and Functions User Guide, available through the Help menu in Numbers.

Regards,

Barry

Hi Eric,

Here's an edited formula that will index any line whose code in column a includes the chosen character (or string fo characters) set in A! of the Breakout Table. Note that I've changed the codes, and reduced the pop-up choices to individual letters. Each of the three letters in the example is included in six of the codes.

=IFERROR(IF(FIND(Breakout :: $A$1 ,A2)>0,MAX($E$1:E1)+1,""),"")

OR

=IFERROR(IF(FIND(Breakout :: $A$1 ,A2)>0,MAX($E$1:E1)+1),"")

If the string is found, FIND returns a number corresponding to the position of the first character of the search-string in the source-string, which is always greater than 0, and the if-condition is evaluated as TRUE.

If the string is not found, FIND returns an error, which is trapped by IFERROR, and the formula returns a null string. FIND will never return a zero or less than zero value, so the if-false argument of IF may be left out of the formula (as is done in the second version).

Note that FIND is case sensitive; set to find “M”. it will not find “m”. For multiple-character strings, there must be an exact match. FIND(“ab”,source-string) will return a number for “abc” or “babz”, but will return an error for “BAC”, bABz”, “4a b5” or “a b c”.

Although no example is shown above, the formula will also index longer codes containing the target letter in any position.

Regards,

Barry

I've tried to use IF(A="*M*") to produce the index column but it won't accept this, although I did get it working with IF(A="M").

Though you already have it working, another solution to your question (if I understood what you want) is

IF(ISERROR(SEARCH("M",A)), "doesn't contain an M", "does contain an M")

another, which is case sensitive but doesn't require the catching of an error, would be

=IF(SUBSTITUTE(A,"M","")=A, "doesn't contain an M", "does contain an M")

And Barry's, which is also case sensitive, written a different way

=IF(ISERROR(FIND("M",A)), "doesn't contain an M", "does contain an M")