Converting row to table, then repeating (copying) table...

I just completed a workbook for our NFL pool at work. I got mildly hung-up on one task, and I am presuming there has got to be a better way to accomplish this. It goes as follows:

On worksheet #1 I have everybody's picks in row format -- 40 entries total -- for ease of entry:


- - NAME | N 1 | N 2 | N 3 | N 4 | A 1 | A 2 | A 3 | A 4 | TIE
_1. Dave | NYG | SEA | GB_ | DAL | SD_ | CLE | JAC | NE_ | IND
_2. Mark | SEA | WAS | GB_ | NYG | SD_ | NYJ | PIT | IND | NE_
_3. Mary | ARI | TB_ | DAL | GB_ | SD_ | IND | BUF | PIT | SF_
...
40. Zack | ...


On the second worksheet, I'm tracking team wins.

On a third sheet, I list everybody's picks in table format, and add in the wins, to print and post up as a weekly update:


1 |Dave| ________ |Wk1|Wk2|Wk3|...|Wk17
. |NFC | Giants__ | 1 | _ | _ |...
. | .. | Seahawks | - | _ | _ |...
. | .. | Packers_ | 1 | _ | _ |...
. | .. | Cowboys_ | 1 | _ | _ |...
. |覧覧覧覧覧覧覧覧覧覧覧覧覧覧覧覧
. |AFC | Chargers | - | _ | _ |...
. | .. | Browns__ | - | _ | _ |...
. | .. | Jaguars_ | - | _ | _ |...
. | .. | Patriots | 1 | _ | _ |...
. |覧覧覧覧覧覧覧覧覧覧覧覧覧覧覧覧
. | .......Total: | 4 | _ | _ |...
. |覧覧覧覧覧覧覧覧覧覧覧覧覧覧覧覧
. |TIE |
. |BRKR| Colts___ | - | _ | _ |...

2 |Mark| ________ |Wk1|Wk2|Wk3|...|Wk17
...


Simple enough, but on this 3rd worksheet, I want to be able to easily copy this simple 2-dimensional table, one after the other after the other, 40 times down the page. The problem is that each table takes 14 rows, so under normal copy-paste technique, "Name" on my second table will refer to the 14th player on my first worksheet, etc.

I managed a tricky little work-around with this, where I've assigned a #1 in front of the first table, and a formula in front of each subsequent table scans up the column and adds one, so they number themselves 1 thru 40 as I paste them down the worksheet. Then each person's name is looked up on the first worksheet, using the INDIRECT function + a formula involving the table #. From there, their 9 team picks are carried over via VLOOKUP from their data page, and team wins are similarly brought in from the team win page.

All fine and good, but using INDIRECT removes a lot of portability from the table, since it's an absolute reference. I can't indiscriminately lengthen the table or jostle around the layout of the entry sheet without screwing up the output. I'm also concerned that when I come back to this next season, it's going to be a huge hassle (attempting to recall what I did to make this work) if we add more entrants.

Anyhow, back to my main question: was there an easier way for me to have done this?

I'd be happy to email you a copy if you're interested in taking a look at it.

- Nate

Hi Nate, and welcome to the board,

could you possibly post a sample of your data as an XL file?
It would be much easier to help you out.
(Personally, I hate having to type all data in a sheet to test the solutions)

Cheers

Originally Posted by arthurbr

could you possibly post a sample of your data

Yep, and thanks... but give me two days, it's buried in a directory at work and I won't be back there until Monday night.

- Nate

Ok, here's a copy of the spreadsheet.

The operation I'm asking about it taking entries from the 5th tab, "Entries - DATA" and turing them into a 2D table on the 1st tab, "Entries - Public".

You'll note that team names in each 2D table drive off of the name of the player (via VLOOKUP), which comes from the reference # in front of each table.

Just working on this today, I managed to greatly simplify the formula used for referencing the DATA sheet. Previously, I utilized the ROW() function and a much more complicated formula to do what it does now with two operators.

Sticking with this simplification would allow me to more easily directly reference each of the 9 team names each player has picked with a simple re-write, eliminating the 9 VLOOKUPs contained in each table.

However, my original question still stands: is there an even more direct way to do replicate these 2D tables in bulk, without the "index numbers" (1-40) in front of each table?

nfl_pool_2008.xls