Help with copying VLOOKUP formula for baseball statistics program

**lionboy23** · 01-10-2012, 12:54 PM

How do I copy a VLOOKUP formula from multiple sequential worksheets to a single sheet?

I am creating an excel file to keep track of baseball statistics. I have one worksheet for each game.

Each game has a box score and player stats for the entire game. Each player has been assigned a number (A1, A2, etc).

Each player also has their own season/career statistic page on their own worksheet. Because each player can bat in a different spot in the lineup, I’ve used a VLOOKUP function to find each player’s row of statistics for each game to carry over to their player page.

Here’s an example of the formula I’ve used:

=VLOOKUP("A1", 'G1'!$B$10:$AI$24, 4, 0) (A1 is player 1, G1 is the worksheet for Game #1, B10:AI24 is the range of stats for the player for every game).

Here is where I get stuck................When I copy the formula down for all the following games (G2, G3, etc) the G1 does not change. Is there a way to do this?

If I type in 3 columns manually and then try to drag and copy, it just copies G1, G2, G3, G1, G2, G3, G1, G2, G3.

Any help with this would be GREATLY appreciated. I hope I’ve explained myself well enough to be understood. I’m learning Excel as I go, so I’m certainly far from proficient with it.

The workbook I've attached with this is just a sample of the entire thing. I have hundreds of pages, but I've just included a single player page and 5 game pages. Hopefully this is enough for someone to understand what I'm trying to accomplish.

Thanks,
Damian

**DBY** · 01-10-2012, 01:12 PM

Hello
Adapt your formula to the following in cell D6 on the P1 sheet and drag down.

=VLOOKUP("A1", INDIRECT("'G"&ROW(A1)&"'!$B$10:$AI$24"), 4, 0)

this should follow the row numbers with the G sheet numbers. Be careful to include all the quotation marks.

Hope this helps.

**JBeaucaire** · 01-10-2012, 01:13 PM

The INDIRECT() function will let you "create references" a few characters at a time. Put this formula in D6, then copy across the to the right and down through row 10:

=VLOOKUP("A1", INDIRECT("'G" & $C6 & "'!$B$10:$AI$24"), COLUMN(), 0)

Now, just format those cells to display numbers in the formats desired.

If you try to go further down than row10, you'll get errors since those sheets don't exist yet.

WARNING: INDIRECT() is a volatile function, this means any time you change any value anywhere in the workbook, every cell with INDIRECT() in it will recalculate. By the time you get to down about halfway through your sheet you will surely be noticing a degradation in performance... things slowing down due to all the recalculating.

RECOMMENDED SOLUTION: I'm going to guess that you're wanting the P1 sheet to eventually be "variable" and you can select any name or code other than "A1" and it will display those stats from the various sheets, so INDIRECT() is the only way to have a SINGLE formula to copy down. A workbook with 205x60 cells, I wouldn't do this.

I would recommend you do what you've been doing, creating a unique formula for each row pointing to the sheets as you add them. You only need ONE formula for each row, though, I've shown you how to use the COLUMN() trick to adjust the 3rd parameter for you.

D6: =VLOOKUP("A1", 'G1'!$B$10:$AI$24, COLUMN(), 0)
D7: =VLOOKUP("A1", 'G2'!$B$10:$AI$24, COLUMN(), 0)
...etc.

Then copy those cells to the right on each row.

VLOOKUP() alone is not volatile, you can add millions of them.

**lionboy23** · 01-10-2012, 01:22 PM

Thank you both so VERY much for your help.

**DBY** · 01-10-2012, 01:26 PM

Thanks JBeaucaire for the warning on the volatile nature of Indirect. I didn't know it had such a performance hit. I'll do a bit of homework on volatile functions.

Regards DBY

**JBeaucaire** · 01-10-2012, 05:49 PM

If you only had a few 100 of those INDIRECT() formulas, it might be tolerable and usable solution. But 205x60... wowser. Try it and you'll see, around 30-40 rows worth you'll start to see minor calculation lags, and it would just get worse as the sheet grows.

Since you can't add formulas past the existing rows vs sheets anyway, might as well just write a clean VLOOKUP formula for each row, then copy to the right.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.