Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replacement Vlookup Formula
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replacement Vlookup Formula
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 On Aug 26, 11:36*am, Anto111 wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Replacement Vlookup Formula
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 "Pete_UK" wrote: 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 On Aug 26, 11:36 am, Anto111 wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
bulk replacement of cell reference within formula | Excel Worksheet Functions | |||
#N/A Replacement | Excel Discussion (Misc queries) | |||
replacement for IF function? | Excel Discussion (Misc queries) | |||
Replacement | Excel Discussion (Misc queries) | |||
Binder replacement for XP | Excel Discussion (Misc queries) |