315 Views 5 Replies Latest reply: Mar 3, 2013 9:38 AM by Barry
Calculating status...
Currently Being Moderated
Mar 2, 2013 3:37 PM

Not sure if this is possible to do but I will explain what I am trying to do as we'll as possible. Any help would be appreciated.

I am trying to create an order sheet for my work.  I would like one column to contain the name of the items, the next column i want to have pop up lists with numerical values of how many I will need of each. Up to this point I am fine. But now I want to, either in numbers or pages or mail, make it so that what ever items I selected and gave a value it creates a list, excluding the items I had at 0.

So say my list was :

Apples.      2

Oranges.   0

Pears.       3

Melon.      0

Banana.   2

Grapes.   1

I want it to create a list that says

Apples 2

Pears 3

Banana 2

Grapes 1

Is this possible?  I hope someone can help.

Numbers, iOS 6.1.2
• Level 6 (12,580 points)
Currently Being Moderated
Mar 2, 2013 4:21 PM (in response to Drewjsph02)

I think I would simply sort the table by amount (descending) then copy the list and paste into mail (or what ever). You can sort by selecting the menu item "Table > Show Reorganize Panel"

• Level 7 (29,095 points)
Currently Being Moderated
Mar 2, 2013 6:12 PM (in response to Drewjsph02)

Wayne's suggested method of handling this provides a quick and easy solution. Its one failing is that it does not maintain the previous order of your list. If that's a consideration, you may want to use a second table that is filled with the non-zero items, listed in the original order. Here's one example:

The table on the left is named Main, the one on the right is named List.

The transfer is done with the help of two formulas, one on each table.

Main::C2: =IF(B>0,MAX(C\$1:C1)+1,"")

Filled down to the end of column C.

The formula creates an index, giving a serial number to each row that contains a quantity greater than zero in column B.

Column C may be hidden.

List::A2: =IF((ROW()-1)<=MAX(Main :: \$C),LOOKUP(ROW()-1,Main :: \$C,Main :: A),"")

Filled right to B2, filled down to the end of the columns.

The lookup uses the index created above to determine the rows from which to return the values in column A of Main. Filled right to column B, it uses the same index to return the appropriate values from column B.

Regards,

Barry

• Level 7 (28,135 points)
Currently Being Moderated
Mar 3, 2013 6:42 AM (in response to Drewjsph02)

Drew,

See:

Distinguishing Absolute and Relative Cell References

Use absolute and relative forms of a cell reference to indicate the cell to which you want the reference to point if you copy or move its formula.

in the Numbers User Guide PDF. You can download it from the Help menu, and it's free.

As you can see from the clip I posted, the dollar sign sets the "Absolute" quality of a cell reference.

Jerry

• Level 7 (29,095 points)
Currently Being Moderated
Mar 3, 2013 9:38 AM (in response to Drewjsph02)

Drewjsph02 wrote:

Thanks a lot. This worked perfectly.

Couple of questions though.  What is 'C\$' or '\$C'

also in the second formula why is it (( Row()-1)   I don't understand the double parentheses between Row and -1

The \$ is an operator than makes the letter or number following it an absolute reference. The easiest way to demonstrate the result is to place formula 1 into cell B2, fill it down to B6 and right to E6, then observe the changes in the formula from cell to cell in that block.

Then replace the formula in B2 with version 2, and repeat the exercise. Then repeat with the other two versions.

1: =A1

2: =\$A\$1

3: =A\$1

4: =\$A1

ROW is a function. All functions include () after the name as a container for the arguments applied to that function. ROW() is one of several functions that either accept no arguments, or that will accept, but do not require an argument.

ROW(B7), for example, will return 7

With no argument specified, ROW() will return the row number of the row containing the function.

Details on this and other functions supported in Numbers can be found in the iWork Formulas and Functions User Guide. This guide, and the Numbers '09 User Guide, may be downloaded through the Help menu in Numbers '09.

Regards,

Barry

#### More Like This

• Retrieving data ...

#### Legend

• This solved my question - 10 points
• This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. 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 Apple Support Communities Terms of Use.