I want one table to mirror another table Categorized by the information in one column.

Question

Level 1

8 points

I want one table to mirror another table Categorized by the information in one column.

This is a simple Spreadsheet I will use to enter Data on My iphone to icloud.

Producer	Feild	Date	Grain Produced	Bushels	Storage	Notes	Acres	Bushels per acre
GFL	Bin Quarter	09/18/2013	Wheat	3,000	Hopper 1 6000S		100	30

I will continue to add lines of information as each feild is completed and produces grain to put into storage locations. I would like this information to be displayed on another table on another sheet of my i phone Summarized by my storage locations so i can quickly see whats in each storage container like hopper 1 6000S, Hopper 2 6000, Hopper 3, 6000 Etc. As i enter Grain from Feilds into Storages I would like the lower Spreadsheet to Automatically be updated. If another Feild produces Grain that also goes into Hopper 1 6000s later on in the season then it should be displayed on another line directly under the earlier entry line for hopper 1 6000S, even if grain has been entered into hopper 2 or hopper 3. Is there any way to do this. Thanks for any advice.

Storage	Grain Produced	Bushels	Feild	Producer	Date
Hopper 1 6000S	Wheat	3000	Bin Quarter	GFL	Sep 18, 2013
Hopper 2
Hopper 3

numbers-OTHER, iOS 6.1.3

Posted on Aug 18, 2013 2:31 PM

Reply

Answer 1

Barry

Level 9

60,023 points

Aug 19, 2013 12:07 AM in response to FarmerNeil

Hi Neil,

Not too difficult, although placing the columns in the same order on both tables would make the formulas a bit simpler.

The solution below was developed using Numbers '09 (for Mac).

Data table is entered data except for columns I (**/Acre) and J (Index)

I2 (and filled down): =IF(LEN(H)>0,ROUND(E2/H2,0),"")

This calculates bushels/acre, then rounds the result to the nearest full bushel. The IF statement suppresses the calculation until an entry has been made in column H (Acres).

J2 (and filled down):

=IF(F="H1",COUNTIF($F$1:F2,"H1"),IF(F="H2",100*COUNTIF($F$1:F2,"H2"),IF(F="H3",1 0000*COUNTIF($F$1:F2,"H3"),999999)))

The formula creates a numerical index to determine the order in which data is listed on the Summary table. This is a 'working' column, and may be hidden. The large number, 999999, is used as a flag to tell the formula on the table Summary that the end of the data has been reached.

Both formulas are filled down from Row 2 to the ends of their respective columns.

The Summary table has a single formula, entered in cell A2 and filled right to column F (Date), and down to the end of the columns:

A2: =IF(SMALL(Data :: $J,ROW()-1)<999999,OFFSET(Data :: $A$1,MATCH(SMALL(Data :: $J,ROW()-1),Data :: $J,0)-1,MATCH(A$1,Data :: $1:$1,0)-1),"")

SMALL is used to order the transfer of the data from Data to Summary starting with the row whose Index value is smallest.

The first MATCH is used to determine the position in column J of each index value as it is needed; the position number is then used to determine the number of rows below cell A1 the required data lies.

The second MATCH does much the same, searching row 1 of Data for the correct column name, and returning its position. That result is used to determine how many columns to the right of cell A1 the required data lies.

As mentioned above, this was developed in Numbers '09 on a Mac. I'm assuming it will also work in Numbers for iOS. If not, let me know in a reply, and I'll request the discussion be transferred to the iWork for iOS community.

Regards,

Barry

PS: I've shortened the Storage names to keep the formulas a bit shorter (and save me some typing). The full names should work just as well. Note that names in Row 1 of both tables must be exact matches. (I also corrected the spelling of "Field" on both tables.)

B

Reply