## Duplicate multiple sheet entries onto 1 new sheet automatically

286 Views 3 Replies Latest reply: Oct 23, 2012 11:11 PM by Barry
Calculating status...
Currently Being Moderated
Oct 22, 2012 7:35 AM

Sheets 1 has separate tables for average, top score etc. I want to display all items in 1 new table. When the separate tables are updated to there new rank order I want it to display the same reorganized data in my new sheet in the correct order.

Shot 1 shows the separate tables

Shot 2 show them all in 1 table.

When any of the separate tables are updated and ranked in order I want them to mirror that on the complete table without having to copy and paste

If I just say: A2= table 1:A2 this works but when table 1 is updated and they move position table 2 keeps them in the same place but with the updated data and they could then be in the incorrect order…

Help

• Level 7 (28,825 points)

Hi 'skittles,

Looks like a set of OFFSET functions would work here. One fly in the ointment is that movement column in the Highest Rough table.

I would include an extra column in the Total Overall table to simplify the larger table by removing the need to split or merge cells in the bottom half.

Table names (L to R for the initial six): HRS, HO, HA3, TO, 1HR, 2HR

I've kept these short only to shorten the display of the formulas.

Large Table: Summary.

Row 10, and columns E, I, and M contain no formulas. Fill with your chosen border colour.

Formulas:

Summary::A1: =OFFSET(HRS::\$A\$1,ROW()-1,COLUMN()-1

Summary::A11: =OFFSET(HRS::\$A\$1,ROW()-11,COLUMN()-1

Fill A1   Right to column D, Fill down to row 9 (Steve Clark, in your example)

Fill A11 Right to column D, Fill down to row 9.

Summary::F1: =OFFSET(HRS::\$A\$1,ROW()-1,COLUMN()-6

Summary::F11: =OFFSET(HRS::\$A\$1,ROW()-11,COLUMN()-6

Summary::J1: =OFFSET(HRS::\$A\$1,ROW()-1,COLUMN()-10

Summary::J11: =OFFSET(HRS::\$A\$1,ROW()-11,COLUMN()-10

Fill each right and down as described for first pair.

Regards,

Barry

Note: This hasn't been checked, as I didn't take the time to reproduce your data.

• Level 7 (28,825 points)

Have you tried it?

I do see an oversight in my formulas—I neglected to change the table name in each to match the name of the small table in shot 1 from which each formula was to collect data. Here are the revised formulas, using the table names I listed above:

Table names:

HRS, HO,   HA3,

TO,   1HR, 2HR

Formulas:

Summary::A1: =OFFSET(HRS::\$A\$1,ROW()-1,COLUMN()-1

Summary::A11: =OFFSET(TO::\$A\$1,ROW()-11,COLUMN()-1

Fill A1   Right to column D, Fill down to row 9 (Steve Clark, in your example)

Fill A11 Right to column D, Fill down to row 9.

Summary::F1: =OFFSET(HO::\$A\$1,ROW()-1,COLUMN()-6

Summary::F11: =OFFSET(1HR::\$A\$1,ROW()-11,COLUMN()-6

Summary::J1: =OFFSET(HA3::\$A\$1,ROW()-1,COLUMN()-10

Summary::J11: =OFFSET(2HR::\$A\$1,ROW()-11,COLUMN()-10

Fill each right and down as described for first pair.

Apologies for the lack of attention to detail.

Regards,

Barry

#### More Like This

• Retrieving data ...

#### Legend

• This solved my question - 10 points
• This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.