Replacement Vlookup Formula

[Anto111]

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

 

[Pete_UK]

Change your formula to this:

=VLOOKUP($J$2,INDIRECT("'[Prozone Data.xlsx]Match "&ROW(A1)&"'!$C$7:$EV
$82"),COLUMN(B1),0)

Then copy across and down as required. This relies on the Prozone
Data.xls file being open at the same time, as INDIRECT will not work
with closed files, but as you do not show the path then I presume that
it is open.

COLUMN(B1) will initially return 2, but as the formula gets copied
across, this changes to COLUMN(C1), COLUMN(D1) etc, which return 3, 4
etc.

Similarly, ROW(A1) will initially return 1 to be added to "Match ",
and this changes to ROW(A2), ROW(A3) etc when the formula is copied
down, thus giving you "Match 2", "Match 3" etc.

Hope this helps.

Pete

 

[Anto111]

Hi Pete,

Thanks for your time, I really appreciate your help. Im certain that i'm
nearly there, its most likely me completely not getting the formula, but when
I use it, it is returning #REF! and the formula highlights cells A1 and B1 on
the summary sheet. Is there anything I have missed or anything I can fix?

Many thanks again,

Ant

Change your formula to this:

=VLOOKUP($J$2,INDIRECT("'[Prozone Data.xlsx]Match "&ROW(A1)&"'!$C$7:$EV
$82"),COLUMN(B1),0)

Then copy across and down as required. This relies on the Prozone
Data.xls file being open at the same time, as INDIRECT will not work
with closed files, but as you do not show the path then I presume that
it is open.

COLUMN(B1) will initially return 2, but as the formula gets copied
across, this changes to COLUMN(C1), COLUMN(D1) etc, which return 3, 4
etc.

Similarly, ROW(A1) will initially return 1 to be added to "Match ",
and this changes to ROW(A2), ROW(A3) etc when the formula is copied
down, thus giving you "Match 2", "Match 3" etc.

Hope this helps.

Pete

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