Autofill VLOOKUP function

**gutter_mutt** · 09-26-2010, 03:43 PM

I've got a workbook that tracks the weekly performance of my team members in each category they are scored on. Every week I add a new worksheet with their performance data. The first worksheet, entitled aggregate needs to compile the data from each consecutive worksheet (week 09, week 10, week 11, etc...)

The function needs to be VLOOKUP because team members can change from week to week, so a progressive cell value formula would be no good. The first cell in the aggregate worksheet uses the following formula:

=VLOOKUP(A2,'Week 09'!A1:I24,2,FALSE)

each consecutive cell from left to right needs to go like this

=VLOOKUP(A2,'Week 09'!A1:I24,2,FALSE)
=VLOOKUP(A2,'Week 10'!A1:I24,2,FALSE)
=VLOOKUP(A2,'Week 11'!A1:I24,2,FALSE)

and going from top to bottom

=VLOOKUP(A2,'Week 09'!A1:I24,2,FALSE)
=VLOOKUP(A3,'Week 09'!A1:I24,2,FALSE)
=VLOOKUP(A4,'Week 09'!A1:I24,2,FALSE)

so the formulas in each cell in a 3x3 grid would appear as follows

=VLOOKUP(A2,'Week 09'!A1:I24,2,FALSE) =VLOOKUP(A2,'Week 10'!A1:I24,2,FALSE) =VLOOKUP(A2,'Week 11'!A1:I24,2,FALSE)
=VLOOKUP(A3,'Week 09'!A1:I24,2,FALSE) =VLOOKUP(A3,'Week 10'!A1:I24,2,FALSE) =VLOOKUP(A3,'Week 11'!A1:I24,2,FALSE)
=VLOOKUP(A4,'Week 09'!A1:I24,2,FALSE) =VLOOKUP(A4,'Week 10'!A1:I24,2,FALSE) =VLOOKUP(A4,'Week 11'!A1:I24,2,FALSE)

however, Excel autofill makes them go like this

=VLOOKUP(A2,'Week 09'!A1:I24,2,FALSE)  =VLOOKUP(B2,'Week 09'!B1:J24,2,FALSE)  =VLOOKUP(C2,'Week 09'!C1:K24,2,FALSE)
=VLOOKUP(A3,'Week 09'!A2:I25,2,FALSE)  =VLOOKUP(B3,'Week 09'!B2:J25,2,FALSE)  =VLOOKUP(C3,'Week 09'!C2:K25,2,FALSE)
=VLOOKUP(A4,'Week 09'!A3:I26,2,FALSE)  =VLOOKUP(B4,'Week 09'!B3:J26,2,FALSE)  =VLOOKUP(C4,'Week 09'!C3:K26,2,FALSE)

There are 6 sections on the aggregate worksheet that need the same thing, with an average of 20 team members and 52 weeks, that means if I have to fill each cell individually with its formula, I'd have to fill 6240 cells. How can I get excel to autofill each cell with the formula that I want?

I've attached a copy of my workbook (I've changed my team members names to user1, user2, etc...) in case it helps.

**Vinodsralian** · 09-26-2010, 03:52 PM

=VLOOKUP(A2,'Week 09'!A$1:I$24,2,FALSE)
=VLOOKUP(A3,'Week 09'!A$1:I$24,2,FALSE)
=VLOOKUP(A4,'Week 09'!A$1:I$24,2,FALSE)

Change this accordingly and drag. Hope it helps!!

**gutter_mutt** · 09-26-2010, 04:05 PM

I tried it, it changes the right value in the formula, but instead of returning the correct cell values, it's simply copying the value of the cell I'm dragging and pasting it into each cell I drag it into.

**Vinodsralian** · 09-26-2010, 04:23 PM

You shouldn't drag it to right in your case, just place them in individual column and drag it vertically
B2=VLOOKUP(A2,'Week 09'!A$1:I$24,2,FALSE)
C2=VLOOKUP(A2,'Week 10'!A$1:I$24,2,FALSE)
D2=VLOOKUP(A2,'Week 11'!A$1:I$24,2,FALSE)

**gutter_mutt** · 09-26-2010, 04:33 PM

That's what I did, although the formula in the cell is changing correctly, it's copying cell values. Using my attached workbook, if I put =VLOOKUP(A2,'Week 09'!A$1:I$24,2,FALSE) in cell B2 and then drag it to cell B23, the formula in each cell will appear correct, but the cell value 0:00:00 appears in each cell. If I drag from B3, then 0:00:19 appears in each cell

**martindwilson** · 09-26-2010, 05:26 PM

try
=VLOOKUP($A2,INDIRECT("'week"&TEXT(COLUMN(I1),"00")&"'!a1:i24"),2,FALSE)

**gutter_mutt** · 09-26-2010, 05:33 PM

exactly how it is? or am I supposed to change something? I copy and pasted exactly that formula and it just displayed the formula as the cell value

**martindwilson** · 09-26-2010, 07:41 PM

type it in, its either pasting as text/the cell is formulated as text/or calculation is set to manual.

**gutter_mutt** · 10-01-2010, 07:05 AM

Thanks for all the help, Vinodsralian's solution ended up working, for some reason auto calculate and manual calculate don't work with this formula, after I have pasted the new weeks values I have to highlight the relevant section in the aggregate sheet and click on calculate in the lower left, then the rest auto calculate for the week.

I really appreciate both of you taking the time to help on this one, it has brought up a new challenge, I will try to tackle by myself, but I may come back and make a new post if I can't figure it out!