Creating a dynamic table

Question

Level 1

4 points

Creating a dynamic table

I have seen this done with Numbers '08 so I know it is somehow possible, but I don't know where to even begin. Here is what I would like to accomplish:

I am looking to create a dynamic table based on a set of criteria on one sheet using the data from either a the same table, or another table .

ex.

The Data sheet has a table containing a range of information such as this:

I would like the reporting sheet to display a table with only the rows whose cost is below 10, or above 10 based on a seperate table with a popup menu containing the options A or B respectively.

I am mainly having an issue hiding the cells that don't meet my desired criteria depending on what the popup menu selection is, or is it possible to create a table on the fly using only the desired rows from the Data table?

Like I mentioned, I witnessed this using Numbers '08 and I am certain it involved some very complicated formulas and functions.

Please Help!

Numbers '09-OTHER, Mac OS X (10.7.2)

Posted on Nov 10, 2011 11:37 AM

Reply

Answer 1

Nov 10, 2011 1:51 PM in response to kenelevn

Ken,

Here's a solution that combines the above approaches.

You can see that a single column has been added to signal the display criteria. The expression in the Marker column, in the first example, is:

=IF(LEN(A)=0, "", IF(OR(AND(A<10, Selector0 :: $A$1="A"), AND(A>=10, Selector0 :: $A$1="B")), "X", ""))

The Marker column may be hidden. Here's the reorganize panel for all cases:

Regards,

Jerry

Reply

Answer 2

Badunit

Level 7

30,708 points

Nov 10, 2011 1:15 PM in response to kenelevn

Add two new columns to your data table (which I am calling Table 1). You can hide them later.

Column D =IF(AND(A<10,A<>""),ROW(),"")

Column E =IF(AND(A>10,A<>""),ROW(),"")

In Table 2:

Cell A1 has your popup

Columns B-D are Cost, Profit, Qty

Cell B2 =IFERROR(LOOKUP(SMALL(IF($A$1="Below 10",Table 1 :: $D,Table 1 :: $E),ROW()-1),IF($A$1="Below 10",Table 1 :: $D,Table 1 :: $E),Table 1 :: A),"")

Fill B2 to the rest of the table to complete it. If you have any blank rows in your data table, you will have blue warning triangles in Table 2 which you will have to click on to ignore.

Note: Your specification did not include what to do if the cost was exactly 10. The formulas above will never return a row where the cost is equal to 10

Reply

Answer 3

Nov 10, 2011 1:05 PM in response to kenelevn

I think you want to use the "Reorganize" button in the toolbar:

This allows you to:

1) sort

2) filter data

Reply