Pulling data from individual tables to compile summary spreadsheet.

Question

Level 1

4 points

Pulling data from individual tables to compile summary spreadsheet.

I have created a form/table in Numbers to record information (register number, provenance, description, value, measurements, etc.) on individual artifacts in our small museum (@ 1,000 items). I would like to be able to pull some of the data from the individual forms/tables to create a summary spreadsheet. I have essentially done this, but not efficiently.

I have used the following formula to pull the data from the first form/table =1.01.01::B1 (which pulls the accession number from the first table and moves it into the spreadsheet under the column "Accession #). I use similar formulae to pull the rest of the data "Collection", "Functional Classification", "Object", "Compositional Classification", etc. It works fine.

Here's the issue I have . . . in order to pull this same data for row 2 in the spreadsheet from the second form/table I have to manually type in the proper formulae for each cell all over again, but using the new form/table desingation; thus =1.01.02::B1 (for the accession number), etc.

How can I tell each succeding row to pull the data from each new form/table without manually typing in each formula? (Just not practical.) Is this possible?

A related question . . . should I create each form/table on a separate sheet, or should every form/table be on the same sheet, or does it not matter?

I have attached a couple of screen shots.

The first image is the form/table for the first artifact, a krater (a serving vessel), item 1.01.01.

The second image is the form/table for the second artifact, a bowl, item 1.01.02.

The third image is the spreadsheet where I have successfully imported the appropriate data from form/table 1.01.01 and have begun importing the data from form/table 1.01.02 (I have only imported the accession number for this row so far). I am having to manually put the formula for each cell to grab the data from each table.

Is there a "magic" formuala that will tell the spreadsheet to go to each individual, successive form/table and get the proper data?

Thanks!

MacBook Pro, Mac OS X (10.6.4)

Posted on Sep 27, 2013 10:43 PM

Reply

Answer 1

Best reply

Barry

Level 9

59,861 points

Sep 28, 2013 1:56 AM in response to Trey Thames

Hi Trey,

Are you going to want the items on the 'summary' table to always be ordered by accession No.?

Do you want all items to be listed on this table?

Copying the data from each 'form' table onto the 'summary' table can be done, but 1000 form tables plus the summary is going to give you a very large file, with lots of places to go wrong. A more efficient model, at least as far as the file itself is concerned, would be to enter your data on the large table, using a single line for each item, then create as many 'small' tables as you'll need at one time to extract and view the data as an 'accession card.'

Whichever direction you go, the accession number is the only value that will be unique to each item, so it should be the key to your retrievals; constructed rather than 'imported.'

For the current scheme, your Form tables are named with the accession number for the item recorded. Assuming the series runs from 1.01.01 to 1.01.99, then to 1.02.01, the series can be easily constructed using a fairly simple formula:

A2: ="1.01."&RIGHT("0"&ROW()-1,2)

Filled down to row 100, that will give you all of the accession numbers from 1.01.01 to 1.01.99

Question: What happens then? Is the next number 1.02.00, or is it 1.02.01?

Once you have that number, it can be used in the formulas in each column to direct Numbers to the correct table for data to be imported to that row.

Here's an example. I've done only the first two columns of data to demonstrate the pattern, and have changed the values on 1.01.01 to make them different from those on 1.01.02. Onlt the data to be transferred in the demonstration, plus the labels for that data, have been included on the 'Form' tables.

There are two formulas on the Summary table, one in C2, the other in D2. Both automatically adjust as they are filled down the columns. (I have filled them only to the next row, which has an accession number for whch there is a target table.)

C2: =INDIRECT($A2&"::B6")

D2: =INDIRECT($A2&"::D3")

Formulas in the rest of Summary::row 2 will be identical except for the cell location of the required datum on the target tables.

When you have completed all the formulas in that row, select cells C2:J2 and Fill down.

Details on INDIRECT may be found in the iWork Formulas and Functions User Guide. The Guide may be downloaded via the Help menu i Numbers.

Regards,

Barry

Reply

Answer 2

Sep 28, 2013 5:28 AM in response to Trey Thames

Trey,

Barry has done a great job of laying out the procedure for creating cell references using the functions INDIRECT and ROW() and the Concatenation Operator. I'll just add a couple of comments.

Whether you have one Sheet per form (Table) or one Sheet for all your Tables, is a matter of personal preference. I would choose to have just one Sheet for all the like Tables and another Sheet for the Summary Table. That would give you a more compact Sheets Pane display and, I feel, would be easier to navigate. Remember that you can drag the table names around within the listing under the Sheet Name to get the order you like.

To help you understand the INDIRECT function...

Indirect takes a string of text characters and converts it to an address token. They both look the same, but to be used in a cell reference the string must be converted to an address. As used here, INDIRECT's argument is computed based on its position in the summary table.

Finally, a small housekeeping point. Trim your form tables so that they don't have excess rows. It just looks neater, and you won't be reminded of those less elegant Excel sheets ;-)

Regards,

Jerry

Reply

Answer 3

Trey Thames Author

Level 1

4 points

Sep 28, 2013 10:49 AM in response to Barry

Barry,

Thank you very much for your reply. This worked perfectly! I need to study the formulae so that I understand WHY they work the way they do.

Follow up questions:

1. For some reason any place I have a number (for instance for Length or Width, or a Year, etc.) the number transfers over to the summary table with a $ sign making it look like a monetary value. I checked and in neither the original form or the summary do I have the $/monetary value checked. So, what have I done to make this like this and what can I do to fix it?

2. Is it possible to hide columns from view so that I can have a smaller table with only very important data columns visible so that I can print on to one page and not have all the extra data "baggage?"

Again, thanks a million!

Trey

Reply

Answer 4

Trey Thames Author

Level 1

4 points

Sep 28, 2013 10:52 AM in response to Jerrold Green1

Jerrold,

Thank you so very much for your response. You and Barry helped me incredibly. I appreciate the support!

I asked the following 2 follow-up questions to Barry:

Follow up questions:

1. For some reason any place I have a number (for instance for Length or Width, or a Year, etc.) the number transfers over to the summary table with a $ sign making it look like a monetary value. I checked and in neither the original form or the summary do I have the $/monetary value checked. So, what have I done to make this like this and what can I do to fix it?

2. Is it possible to hide columns from view so that I can have a smaller table with only very important data columns visible so that I can print on to one page and not have all the extra data "baggage?"

Trey

Reply

Answer 5

Sep 28, 2013 1:11 PM in response to Trey Thames

Trey,

1. Check the format and make sure it isn't set to "Currency". If that's not it, please post a Screen Shot so we can see the problem.

2. Read Chapter 3 of the Numbers User Guide PDF, a free download from the Help menu. See particularly the section on page 59: Working With Rows and Columns in Tables.

In fact, the first four chapters are recommended reading for all new Numbers users.

Jerry

Reply

Answer 6

Trey Thames Author

Level 1

4 points

Sep 28, 2013 1:36 PM in response to Jerrold Green1

Jerrold,

I have made sure that those cells are not set to currency. I have attached screen shots.

Again, thanks for the help.

Trey

Reply

Answer 7

Sep 28, 2013 2:03 PM in response to Trey Thames

Trey,

I don't see any clues in the screen shots, unfortunately. If you'd like, send me a sample file demonstrating the problem and I'll look closer.

Jerry

Reply

Answer 8

Sep 28, 2013 2:33 PM in response to Jerrold Green1

Trey,

Changing the format for those columns to "Number" rather than "Automatic" solves the problem. I don't know why the Automatic format defaulted to currency, but forcing it to Number (plain Number, not Number System) does the trick.

It's possible that there was some Excel heritage to this file because I got a font substitution error involving the standard Excel font. That could explain some of the trouble.

Jerry

Reply

Answer 9

Trey Thames Author

Level 1

4 points

Sep 28, 2013 2:46 PM in response to Jerrold Green1

Jerry,

Thanks much! That worked, though I thought I had tried that already without success. But it is working now. I'm not sure about the Excel heritage possibility. I thought I created this file from scratch this morning, but maybe I had imported. Can't remember now.

Thanks, again.

Trey

Reply

Answer 10

Barry

Level 9

59,861 points

Sep 28, 2013 7:17 PM in response to Trey Thames

HI Trey,

I don't see any clues to the formatting either. It has the appearance of "Accounting style" regarding the position of the $ sign, but surprised me by having no places after the decimal. I was expecting to see the usual two places.

Jerry's suggestion is the same as I would have made—format as Nuber, with no decimal places.

Glad to see you have it working.

Regards,

Barry

Reply

Answer 11

Trey Thames Author

Level 1

4 points

Sep 28, 2013 7:20 PM in response to Barry

Barry,

Thanks! Two big thumbs up!

Trey

Reply