Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

If checkbox is checked, copy corresponding cells and list them in another sheet.

Hi,


I have a shopping list that we use at our store to create order list. Recently I have created a numbers version of the shopping list.


I want to be able to check mark a checkbox and in a separate sheet the corresponding cells to be listed. Here is an image of my list:


User uploaded file

So for example:


If chicken breast, butter and avocado are checked in this sheet, on a separate sheet they are listed with the corresponding number in front of them (the number cell is manually entered)


Can anyone help?


Thank you very much!

MacBook Air, iOS 10.0.3

Posted on Oct 22, 2016 7:58 AM

Reply
6 replies
Sort By: 

Oct 24, 2016 12:49 AM in response to sinahamidi

Hi sinahamidi,


Are the numbers entered in the cells currently containing zeros on the table pictured?


What is the "order list" to look like? Can you show an example containing items from several of the categories on the shopping list?


What happens with the order list? Does it get printed, emailed or saved, then cleared and used for the next order?


Regards,

Barry

Reply

Oct 24, 2016 6:52 AM in response to sinahamidi

Hi sinahamidi,

It will be easier to achieve your aim if your table looks something like this:

User uploaded file

I have made your table into a list (with only some foods included to make it shorter, just as an illustration).

Menu > File > Save with a new document name just in case the next steps go wrong.


Select the whole table (click once anywhere on the table, then click on the "Bullseye")

Copy.

Insert a new Sheet and delete the default table.

Paste to see a copy of that table.

Rename that table as Shopping list.

In Shopping list, format column C as Automatic.

Enter this formula in C2 (and Fill Down)

Table 1::C2

User uploaded file


Save.

Apply a filter to the new table.

User uploaded file

No Body Rows are yet showing because nothing is checked (ticked) on Sheet 1.

Save as Template for future use

Check (Tick) items in Sheet 1 Table 1 and enter Quantity

User uploaded file

Sheet 2 now shows this:

User uploaded file

Formula in Column D (and Fill Down)

=Table 1::D2

You can hide Column C

User uploaded file


Please call back with questions.


Regards,

Ian.

Reply

Oct 24, 2016 7:23 AM in response to Barry

Hi Barry,


The numbers in the cell are currently zero and are manually entered once the checkbox is ticked.


I'd like the order list to look something like this:

User uploaded file

So once any ingredient in ingredient list is ticked it will be listed in the shopping list (order list). The list then gets exported to PDF and uploaded on cloud where a third party will have access to it to place the order. I know it will be easier to make the ingredient list in a table that @yellowbox suggested, however it is very easy for the person making the list to miss items if the table is so long like that.


Thanks,

Reply

Oct 24, 2016 7:27 AM in response to Yellowbox

Hi Ian,


Thank you for taking the time to illustrate how this can work. Is it possible to keep the ingredient list as is? If we change the way the primary list looks, it becomes prone to making mistakes by missing items when someone is creating the shopping list, because the list becomes too long. Also once the primary list is filled and the shopping list is generated it has to be exported to pdf, so a long list will make the pdf to have several pages (eg. if they order everything on the list one day)


Thank you so much for your help!


Sina

Reply

Oct 24, 2016 5:28 PM in response to sinahamidi

Hi sina,


Here's a possibility. Like Ian, I have simplified your shopping list, and revised the items to let the structure show through.


The solution starts with what Ian has proposed, but adds a step to get from your version of the Shopping list to the form he proposed. Here's one version of the Shopping List entry form and the resulting Order table:

User uploaded file

Note that this version has omitted the headings, except for those that appeared in row 1 of the shopping list, here represented by AAAA, EEEE, etc. (The letters originally identified the column on which they appeared—an auxiliary column containing indicators to Numbers telling it which values to transfer to the Order table. They 'fell out of step' when these columns were removed.)


Here's a variation on the example above that includes the category headers on the order:

User uploaded file

The headings shown in bold were formatted that way in a non-header cell; the ones in the header row of the Shopping List table lost their 'bold' attribute on transfer. A conditional highlighting rule could be used to highlight the rows containing headings.


What's seen above is what is visible on the two tables. Below is a peek at the rest of it.


User uploaded file

Except for cells that appear empty in columns B, E, H, K and N, "Shopping List" contains only entered data.

The 'empty' cells in the columns noted contain the formula

=""

which inserts a null string into the cell, preventing it from displaying a zero when copied to Order.

There are other formulas in my example, but their only purpose is to construct the sample items in the wide columns, which on your table will be entered data.


"Order" is a three column list of the items names (and category names in this example) constructed from the first table. ALL data on this table is copied by formula from "Shopping List" None is entered manually. The only manual intervention needed is to add rows to the end of the table until all of the data from Shopping List is copied.


The copying is done with the formula below, entered into A1, then filled right to C1, then filled down to the last row of the table. The table is then extended in row count until the formula runs out of information to copy from Shopping List, and begins placing error triangles in all three columns.


Order::A1: =INDEX(Shopping List::$A:$O,MOD(ROW()−1,ROWS(Shopping List::$A,0))+1,COLUMN()+3×QUOTIENT(ROW()−1,ROWS(Shopping List::$A)))


Here's how it should look in the Formula Editor:

User uploaded file


As can be seen in the image, any item whose checkbox is checked in Shopping List has TRUE in column C of Order; any item whose checkbox is unchecked has FALSE in column C, and the category names with T in the cell beside them have T in column C of Order.


The Order table is filtered on column C, using the rule below:

User uploaded file


Column C is necessary for the filtering, but does not need to be seen by the user, so it is hidden, and the user sees only columns A and B in the rows shown by the filter.


The first example above is the same as the second with two exceptions:

The filter rule is Show rows where The Text IS "TRUE"

Most headings are not shown.


Regards,

Barry

Reply

Oct 26, 2016 12:48 PM in response to sinahamidi

sinahamidi wrote:



I'd like the order list to look something like this:

User uploaded file

So once any ingredient in ingredient list is ticked it will be listed in the shopping list (order list). The list then gets exported to PDF and uploaded on cloud where a third party will have access to it to place the order.



It sounds as if you will be doing this quite often. If so a script may be a good solution (no extensive knowledge of scripts needed to use; just click and run).


The script below, for example, automatically produces this table on a new sheet that you can then File > Print > Print... and choose PDF as format.

User uploaded file


I produced this by first clicking anywhere in this source table:


User uploaded file


Then clicking the 'Run' button in Script Editor after copy-pasting the script below into Script Editor (in Applications > Utilities). You copy-paste in Script Editor the same as you would in any other application.


The script reads the checked items in the tables, reads their names and quantities, and automatically creates a shopping list table that can be printed to pdf.


If you haven't used Script Editor on your machine make sure Script Editor.app is checked in System Preferences > Security & Privacy > Privacy > Accessibility.


You should know within a minute or two of trying whether this approach will help. Some minor changes to the script may be needed if you've changed your table so that it is now different from your screenshot, but there is no need otherwise to get involved with AppleScript.


Be sure to first click anywhere in the source table first (the one with the checkmarks) before clicking the 'Run' button.


SG


tell application "Numbers"

tell (front document's active sheet's first table whose selection range's class is range)

set vv to rows'scells'svalue-- reads values into AppleScript list of lists (2D matrix) named vv

end tell

end tell


-- parameters are (vv, startRow, endRow, StartCol)

set theProteins to getChecked(vv, 3, 8, 1)

set theFrozens to getChecked(vv, 10, 10, 1)

set theDairies to getChecked(vv, 12, 19, 1)

set theDrinks to getChecked(vv, 21, 26, 1)

set theProduce to getChecked(vv, 3, 22, 4)

set theProduce2 to getChecked(vv, 3, 22, 7)

set theGroceries to getChecked(vv, 3, 26, 10)

set theGroceries2 to getChecked(vv, 3, 26, 13)


set ingredList to theProteins & theFrozens & theDairies & theDrinks & ¬


theProduce & theProduce2 & theGroceries & theGroceries2


tell application "Numbers" to tell front document

set newSheet to makenewsheet


tell newSheet


deletetables-- remove default table

set newTable to make new table with properties {name:"Shopping List", row count:2, column count:2, header column count:0, position:{100, 75}}


tell newTable-- these are cosmetic and could be omitted

set row 1's background color to "white"

set column 2's alignment to center

set cell 1's value to "Ingredient"

set cell 2's value to "Quantity"

end tell


repeat with i from 1 to ingredList's length

tell newTable

tell row (i + 1)

set cell 1's value to ingredList's item i's item 1

set cell 2's value to ingredList's item i's item 2

end tell


add row below last row

end tell

end repeat


end tell

end tell


to getChecked(theData, startRow, endRow, startCol)

set tempLst to {}

repeat with i from startRow to endRow

if theData's item i's (item (startCol + 2)) is true then

set n to theData's (item i)'s item startCol -- name

set q to theData's item i's item (startCol + 1) as integer -- quantity

copy {n, q} to tempLst's end

end if

end repeat


tempLst

end getChecked

Reply

If checkbox is checked, copy corresponding cells and list them in another sheet.

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.