Help with multisheet checkbox formula

Question

Level 1

6 points

Help with multisheet checkbox formula

Hi all!

I'm having a bit trouble figuring out how to get Numbers to do what I want. Running the latest (3.6.1), and I'm trying to make a film shot list/log.

I have some slate info and file info, and a two checkboxes. I'm filling these in by hand, and copying the pre-filled in info via the yellow circle. I would to do two things and I can't figure out how.

I would like it if I could somehow add one to the clip cell. When I change a card in the camera, it starts from _c001, which I would type in and it would then automatically add one to every cell below.

I have one of these sheets for each day. I would like to have a separate extra sheet, which only shows the good takes, and gives all of the info. Meaning, if the checkbox is checked, it shows the whole row, if the checkbox is unchecked, then the row is hidden.

Everything I try, I keep getting syntax errors. Any pointers would be much appreciated!

MacBook Pro, OS X Mavericks (10.9.2)

Posted on Nov 19, 2015 2:33 AM

Reply

Answer 1

Yellowbox

Level 7

20,389 points

Nov 19, 2015 4:11 AM in response to TommyJay

Hi Tommy,

When I change a card in the camera, it starts from _c001, which I would type in and it would then automatically add one to every cell below.

Type _c001 in D2 (I removed your Row 1 with what appear to be merged cells... They can cause trouble with sorting, filters and formulas 😉).

Drag the yellow dot down to fill

if the checkbox is checked, it shows the whole row, if the checkbox is unchecked, then the row is hidden.

The Filter feature will do this. Click on the Column Label G then on the disclosure "v" to see a contextual menu

That shows only the rows with ticked (TRUE) checkboxes in G.

Regards,

Ian.

Reply

Answer 2

TommyJay Author

Level 1

6 points

Nov 19, 2015 4:46 AM in response to Yellowbox

Hi YellowBox!

thanks for your reply. The filter table option helped a lot, thanks for that! But how do I get this in a separate sheet? I want to export the numbers document to a pdf when I'm finished, and I'd like to have each sheet with the day's worth of shots, plus an extra, separate sheet that only shows the good takes. Is it possible to do this with a formula? So that whenever I mark a take as good, it automatically shows up in the separate sheet?

As for the yellow dot, I was wondering if there was a way to do it differently. I've been doing exactly this and it is a great help, but as we can have hundreds of shots per day, and the card number/take number always resets to one then a new card/new scene is shot, it takes time. I was just wondering if there was any way to use some sort of increment formula. My thought would be as follows; in clip number (Column D) I would put some formula for D3 like "=D2+1" and then with the yellow dot drag it down. That way, if I ever have to 'reset' (start from zero), I would just write in the number and the rest below would automatically update to reflect the data I need. Any ideas? I tried several functions and they always returned a syntax error...

Reply

Answer 3

Yellowbox

Level 7

20,389 points

Nov 26, 2015 4:06 AM in response to TommyJay

Hi Tommy,

We need to know what you mean in Column A (for example, 8/1). Is that a date? Numbers will store this internally as 8/1/2015 12:00:00 am. That is how Numbers works.

Try this on one Sheet:

An input table (no filters).

A Header Row (Row 1).

Menu > Table > Freeze Header Rows so that you can scroll the table and still see the Header Row. Just keep adding data day by day.

On another Sheet, a copy of that table with another table (Enter Date) and another column (H) on the main table to insert "Yes" or "No" if the date matches.

Formula in H2 (and Fill Down)

=IF(A2=Enter Date::$A$2,"Yes","No")

Now filter Daily Summary like this:

You can hide Column H before you convert that Sheet to PDF

Regards,

Ian.

Reply

Answer 4

Yellowbox

Level 7

20,389 points

Nov 26, 2015 4:05 AM in response to Yellowbox

Just in case my screen shot looks like this:

here it is again:

Regards,

Ian.

Reply

Answer 5

TommyJay Author

Level 1

6 points

Nov 19, 2015 8:26 AM in response to Yellowbox

Ian thanks so much for your help! I really appreciate your help.

I have frozen the rows as headers, thanks for that, it looks much better now!

As for the data, sorry for not elaborating on that properly. They aren't dates, they're the numbers of the scene and slate. They always increment, until we start a new take/slate/scene. For the sorting purposes, only the 'good' column (G) is important. In the separate sheet, I only want to display the rows which have the G column checked.

I've tried using the filter using 'text is 'true'', and that's great! That's exactly what I need, now to simplify my question; is there any way to automatically take all data from several sheets into one sheet? Meaning, if I have 3 sheets like the one pictured above, is there any formula I can use to put all the data/full sheets one by one in the separate sheet?

Thanks,

Tommy

Reply

Answer 6

SGIII

Level 8

35,330 points

Nov 19, 2015 9:32 AM in response to TommyJay

TommyJay wrote:

is there any way to automatically take all data from several sheets into one sheet? Meaning, if I have 3 sheets like the one pictured above, is there any formula I can use to put all the data/full sheets one by one in the separate sheet?

Hi Tommy,

Would you consider keeping your data in one table? That makes filtering and summarizing much easier.

(BTW, note the difference between a sheet and a table in Numbers. Unlike in Excel, all cells are in a table, not directly in a sheet. You can have multiple tables on a sheet.)

SG

Reply

Answer 7

Yellowbox

Level 7

20,389 points

Nov 19, 2015 8:30 PM in response to SGIII

Hi SG,

Would you consider keeping your data in one table? That makes filtering and summarizing much easier.

Seconded. I have been trying to think of a way that Tommy wants, but it gets messy.

Tommy,

What works best in Numbers is to pull subsets of data from a master table into several small tables, each with a different purpose.

The master table could be the input table (freeze Header rows so you can add each slate to the bottom row).

The subset tables could pull data that matches each slate and "Good".

Regards,

Ian.

Reply

Answer 8

TommyJay Author

Level 1

6 points

Nov 26, 2015 4:05 AM in response to Yellowbox

Hi SG & Ian,

Thanks for your answers. I guess what I wanted wasn't something 'standard enough' for it to have a simple way to do. It's not even that I would've had that much experience with Excel, just wanted to make a very automated spreadsheet (or table?). I've figured a way around it, I just filled in all the takes and data for the day, and then when I was done with the day I proceed with Ian's method, copying it to the new page and using the sort function. Pretty neat! Thanks for the help guys!

Tommy

Reply