Hi guys,

I have a relatively large workbook which is 1000 columns x 50 rows of data x
46 spreadsheets.

Basically each spreadsheet is identical and represents a weekly collection
of person data, each row represents a different person, and each column
represents a different measurement parameter.

I have a summary sheet where I place the persons name is a box from a drop
down menu and that persons data is picked up for the 46 weeks for a
designated parameter, so effectively I can chart individual progress in a
specific parameter over the 46 week period.

I am using VLOOKUP to pick up the data from the original workbook, and
example formula I have is: =VLOOKUP($J$2,'[Prozone Data.xlsx]Match
1'!$C$7:$EV$82,2,0).

Where J2 is the dropdown presons name and match 1 is the spreadsheet in
question with the target data range.

Basically it is taking me forever to write these formulas in the summary
sheet because the formula has to be changed when going along columns in order
to change the lookup identity (so 2 in this case to 3 in the next column to 4
in the next etc right through to 1000), and I have to change the match number
when going down rows (match 1, match 2, match 3 etc). effectively I am
manually altering every formula for 1000 columns x 46 rows.

Is there a way that I can adapt my formula so I can simply drag it and
change it as per usual excel function?

All help would be so much appreciated.

Many thanks in advance,

Ant