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