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| ________
. |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.