Vangard wrote:
Also, is there a way to specify its "existence" to a date range? What I am trying to do is have a separate table show whether or not I have paid the phone bill each month. So without a date range, it will display that I've paid it on every month. (Assuming I can get it to display the proper status in the first place.)
What you're looking for is a "breakout table"
These require an index column on the main table that marks the target rows with an identifier (a serial number) that's used by the LOOKUP function to retrieve other information from the marked rows.
For the example, I've placed Date, category and amount in columns A, B and C of the main table, and used column E as the index column.
On the Breakout table, the Category is in A1, dates are listed under it, and amounts in column B.
Formulas:
Table 1
E2 and filled down the column: =IF(B=$E$1,MAX($E$1:E1)+1,"")
Table 2
A2 and filled down the column: =IF(ROW()-1>MAX(Table 1 :: $E),"",LOOKUP(ROW()-1,Table 1 :: $E,Table 1 :: $A))
B2 and filled down the column: =IF(ROW()-1>MAX(Table 1 :: $E),"",LOOKUP(ROW()-1,Table 1 :: $E,Table 1 :: $C))
The IF part is used to blank cells where LOOKUP would be looking for a number greater than the maximum value in the index column. Because LOOKUP cannot be set to look for exact matches only, it would repeat the last found entry if this part of the formula were omitted.
Note that the values (text) in the category column, and cell E1 of Table 1 must match exactly for the row to be included in the breakout table. (This may also be the reason for the 'missing' message you are getting from Yvan's formula.) The easiest way to accomplish this is to enter the category labels using a pop-up menu, and to use the same menu in E1.
Extension: For a single breakout table with a choice of extracting any one of the categories, place a copy of the pop-up menu in cell A1 of Table 2, and replace the value in E1 of table 1 with the formula =Table 2::A1
Regards,
Barry
Descriptions of ROW, MAX and LOOKUP may be found in the iWork Formulas and Functions User Guide, which may be downloaded through the Help menu in Numbers.