Creating a spreadsheet for each value - Possible?

Question

Creating a spreadsheet for each value - Possible?

I recently got my iMac and earlier I was looking at the templates for Numbers. If we look at the ''Grade Book'' template, there's a report sheet that goes along with the student data. I was wondering if there was a way to generate a report page for every value in the column of a specific table to ease the process of printing. For example, in that template, the values are the kid's names. You can type the kid's name in the "A" column of the "Student Lookup" table for the ''Reports'' Sheet. It would help to have all of the pages generated automatically instead of typing each name (or value) and then having to print/save a PDF copy for example.

Thanks!

Numbers-OTHER, Mac OS X (10.7.2)

Posted on Oct 29, 2011 11:11 PM

Reply

Answer 1

Barry

Level 9

59,861 points

Nov 1, 2011 12:02 AM in response to Eddie Lives On

Hi Eddie,

What you're describing sounds like a 'mail merge' task. You'll find detailed information about merging data from a Numbers document into a Pages document in Chapter 11 of the Pages '09 User Guide.

Assuming you have the full iWork set of applications, you can download the Pages '09 User Guide via the Help menu in Pages.

Regards,

Barry

Reply

Answer 2

Nov 1, 2011 1:04 AM in response to Barry

Thanks for the reply, Barry,

Well, I'm quite new to the Mac environment so maybe I didn't clearly explain what I'm after. By the way, I use iWork '11. If you look at the Grade Book template, you'll see that when on the second sheet, titled "Reports", you can change the students' names. They are in the table named "Student Lookup", which is the first one. Changing "Jerry Rose" (in cell A3) to "Jon Smith" will change the sheet accordingly, based on the data entered in the first sheet, here titled "Student Data".

It's changed automatically, which is nice. We just have about 15 different values here (or names in this specific case), so printing a copy for each student isn't too hard. But what if I there were 150 values instead of 15? Would I still have to print them out one by one, or would there be a way for Numbers to generate the "Reports" sheet for every single value I put in?

I hope you're not offended about me being very descriptive, I just want to clarify my request as much as possible.

Thank you for your time,

Ed

Reply

Answer 3

Nov 1, 2011 7:02 AM in response to Eddie Lives On

I think what you really want is a workflow or apple script which will insert a value (the students' name) into a cell of the report, which will, then, look up the grades, then print that document to a file (probably PDF). Additionally the script may need to create an email.

This means you would need a list of student names and parent email addresses which the script could look up from a table in your Numbers document

Reply

Answer 4

Barry

Level 9

59,861 points

Nov 1, 2011 8:43 AM in response to Eddie Lives On

I hope you're not offended about me being very descriptive, I just want to clarify my request as much as possible.

Hi Ed,

No offense taken. My error in not picking up on the "grade book template" reference.

Wayne has put you on a right track. The 'list of student names' he mentions can come directly from the Student Grades table itself, or from a separate 'Student Data' table that would include the students' personal data (address, phone, parent names, email, etc.).

An interim possibility, easier (for me-my AppleScript skills are close to nil) to write, would be to add a column to the left of the Student Names column to contain a list of numbers counting off the students, and a similar column in Student Lookup with a Stepper cell to generate the same set of numbers, one at a time. A Lookup formula in the cell (currently A2) containing the name for whom the report is to be generated would used the number generated by the stepper cell to look up the corresponding student.

Still a manual operation, but with the manual part reduced to one click to select the next student plus the clicks or keystrokes needed to print the report.

I'm curious as to where you obtained a copy of "iWork '11," though. Unless I've missed something while I was away, the current edition of iWork for Mac is still iWork '09 (as listed on Apple's iWork page. 😉

Regards,

Barry

Reply

Answer 5

Nov 1, 2011 10:13 AM in response to Barry

By '11 I meant the newest one. I often confuse iLife and iWork!

Thanks Wayne for joining. There wouldn't be a need for emails, but if I were to keep the Grade Book example, I'd just need a PDF file with a report sheet for each student (value), in order to print the reports and hand them out.

I don't know how different scripts are when comparing iWork to Office but I was able to do a few things in Excel.

Where I work, there is a lot of information and statistics that were exported as .txt files and that are quite relevant for what we do. Being able to import those, make graphs and other things to represent the evolution of sales, inventories and prices would help us greatly.

That's one of the things I was never quite able to achieve in Excel. Was I to blame or was it more of a software limitation? That I do not know. What I do know is that my new iMac, coupled with iWork can surely help me.

Thanks!

Reply

Answer 6

Aug 24, 2015 11:50 AM in response to Eddie Lives On

Hi, I think I'm trying to do the same thing you were trying to do in 2011. Were you successful? I have around 500 students and would like to generate a report card for each one. I was hoping to design the report card and have it linked to a spreadsheet to fill in certain information (students name and class) and calculate a grade. Looking forward to hearing from you!

Reply

Answer 7

Yellowbox

Level 7

20,383 points

Aug 25, 2015 4:49 AM in response to kelleefromelmhurst

Hi Kellee,

A small subset of 500 students in Sheet 1

A summary for each student (Report Card) in Sheet 2

Enter a student name in A2

B, C and D will fill in the details from Sheet 1

Formula in B2 =INDEX(Student Details::B,MATCH($A2,Student Details::$A,0))

Formula in C2 =INDEX(Student Details::F,MATCH($A2,Student Details::$A,0))

Formula in D2 =INDEX(Student Details::G,MATCH($A2,Student Details::$A,0))

Menu > File > Print...

Choose Print: This Sheet, then Print... (outlined in red) to go to the Print Dialogue.

Then Click on Done (outlined in blue) to return to your document, Sheet 2.

Enter another student name to create a new Report Card.

With 500 students, might I suggest you have separate documents for each class?

Numbers will not link between documents, but perhaps you don't need that for Report Cards.

Also, calculating Grade (RANK) within a class becomes simpler.

Regards,

Ian.

Reply

Answer 8

t quinn

Level 5

7,043 points

Aug 25, 2015 6:16 AM in response to kelleefromelmhurst

Hi kellee,

If you are wanting to end up with 500 unique files, say pdfs, generated off of a single form that you have created within a spreadsheet, I think the suggestion above that applescript is the way to go is the one you want. We have a couple of applescript gurus who may be able to help.

I would suggest asking a new question as this is a very old thread. You would have to include specifics about how your tables, both the data and the report, are set up and what information needs to be inputted into the report. In Ian's example above it looks like the only input the report needs is a student name in A2 and they all originate in Student Details::A.

quinn

Reply

Answer 9

Aug 30, 2015 1:25 PM in response to Eddie Lives On

Thank you for your responses! I will start a new thread, but first I'll post a copy of how I want the report card to look. This is just the front. They are half sheets to try and save paper and money. For this past school year I filled this all out by hand. Like I said, I'd like to find a way to fill out most fields automatically. Thanks!

Kellee

Reply