Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Vlookup / Match Formula across 8 workbooks
I have a multiple vloookup question. Currently I have a consolidation
model that pulls in data from 3 other workbooks based on 2 conditions: 1: does the user want the submodel included - a "y" or "n" is entered 2: is the consolidation model month number for the row/column we are in = the month number on which we should start including the submodel These conditions do not apply to the first model as it is always included. My formula which appears below has worked fine for three workbooks, but now I am asked to include a total of 8 workbooks making the formula cumbersome and likely (haven't tested yet) running over the formula character limit. All the sheets are formatted the same with month numbers running on line 3 and account names in column B. 1 2 3 .. 120 Accnt 1 Accnt 2 Accnt 3 ... Acct 65 =IF(Model1_Flag="y",VLOOKUP($B6,'Model1.xls'!PLDat aTable,MATCH(M $3,'[Model1.xls]PandL'!$B$3:$EB$3,0),FALSE),0) +IF(AND(Model2_Flag="y",M$3=Model2Start),VLOOKUP( $B6,'Model2'! PLDataTable,MATCH(M$3-Model2Start+1,'[Model2.xls]PandL'!$B$3:$EB$3), 0),FALSE) +IF(AND(Model3_Flag="y",M$3=Model3Start),VLOOKUP( $B6,'Model3.xls'! PLDataTable,MATCH(M$3-Model3Start+1,Model3.xls]PandL'!$B$3:$EB $3,0),FALSE)) I would appreciate any feedback on how to change the formula, us VBA instead, etc. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Vlookup / Match Formula across 8 workbooks
Hi,
I think you lost a ) in the second part of the formula at the end? On the formula side you could reduce this as follows: If you define the following range names in the workbook with the formula you can shorten the formula to the one shown below. Model1.xls'!PLDataTable M1DT '[Model1.xls]PandL'!$B$3:$EB$3 M1PL Model2'!PLDataTable M2DT '[Model2.xls]PandL'!$B$3:$EB$3 M2PL Model3.xls'!PLDataTable M3DT '[Model3.xls]PandL'!$B$3:$EB$3 M3PL There may be some typos in the above, however, don't type them just point and click. Here is how you do this - 1. Open all spreadsheets 2. In the one where your formula is choose Insert, Name, Define 3. Enter M1DT in Names in workbook 4. Click on the Refers to box and navigate to and select the range in the Model1 workbook. 5. Click Add Repeat for the other 5 names. =IF(Model1_Flag="y",VLOOKUP($B6,M1DT,MATCH(M $3,M1PL,),)) +IF(AND(Model2_Flag="y",M$3=Model2Start),VLOOKUP( $B6,M2DT,MATCH(M$3-Model2Start+1,M2PL,),)) +IF(AND(Model3_Flag="y",M$3=Model3Start),VLOOKUP( $B6,M3DT,MATCH(M$3-Model3Start+1,M3PL,),)) I have made a few other simplifications. If this helps, please click the Yes button. -- Thanks, Shane Devenshire "kurt" wrote: I have a multiple vloookup question. Currently I have a consolidation model that pulls in data from 3 other workbooks based on 2 conditions: 1: does the user want the submodel included - a "y" or "n" is entered 2: is the consolidation model month number for the row/column we are in = the month number on which we should start including the submodel These conditions do not apply to the first model as it is always included. My formula which appears below has worked fine for three workbooks, but now I am asked to include a total of 8 workbooks making the formula cumbersome and likely (haven't tested yet) running over the formula character limit. All the sheets are formatted the same with month numbers running on line 3 and account names in column B. 1 2 3 .. 120 Accnt 1 Accnt 2 Accnt 3 ... Acct 65 =IF(Model1_Flag="y",VLOOKUP($B6,'Model1.xls'!PLDat aTable,MATCH(M $3,'[Model1.xls]PandL'!$B$3:$EB$3,0),FALSE),0) +IF(AND(Model2_Flag="y",M$3=Model2Start),VLOOKUP( $B6,'Model2'! PLDataTable,MATCH(M$3-Model2Start+1,'[Model2.xls]PandL'!$B$3:$EB$3), 0),FALSE) +IF(AND(Model3_Flag="y",M$3=Model3Start),VLOOKUP( $B6,'Model3.xls'! PLDataTable,MATCH(M$3-Model3Start+1,Model3.xls]PandL'!$B$3:$EB $3,0),FALSE)) I would appreciate any feedback on how to change the formula, us VBA instead, etc. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sum Vlookup / Match Formula across 8 workbooks
On Oct 23, 10:04*pm, ShaneDevenshire
wrote: Hi, I think you lost a ) in the second part of the formula at the end? On the formula side you could reduce this as follows: If you define the following range names in the workbook with the formula you can shorten the formula to the one shown below. Model1.xls'!PLDataTable * * * * * * * * * * * * M1DT '[Model1.xls]PandL'!$B$3:$EB$3 * * * * * * * * * * * * *M1PL Model2'!PLDataTable * * * * * * * * * * * * * * * * * * * * * * M2DT '[Model2.xls]PandL'!$B$3:$EB$3 * * * * * * * * * * * * *M2PL Model3.xls'!PLDataTable * * * * * * * * * * * * M3DT '[Model3.xls]PandL'!$B$3:$EB$3 * * * * * * * * * * * * *M3PL There may be some typos in the above, however, don't type them just point and click. *Here is how you do this - 1. *Open all spreadsheets 2. *In the one where your formula is choose Insert, Name, Define 3. *Enter M1DT in Names in workbook 4. *Click on the Refers to box and navigate to and select the range in the Model1 workbook. 5. *Click Add Repeat for the other 5 names. =IF(Model1_Flag="y",VLOOKUP($B6,M1DT,MATCH(M $3,M1PL,),)) +IF(AND(Model2_Flag="y",M$3=Model2Start),VLOOKUP( $B6,M2DT,MATCH(M$3-Model2Start+1,M2PL,),)) +IF(AND(Model3_Flag="y",M$3=Model3Start),VLOOKUP( $B6,M3DT,MATCH(M$3-Model3Start+1,M3PL,),)) I have made a few other simplifications. If this helps, please click the Yes button. -- Thanks, Shane Devenshire "kurt" wrote: I have a multiple vloookup question. *Currently I have a consolidation model that pulls in data from 3 other workbooks based on 2 conditions: 1: does the user want the submodel included - a "y" or "n" is entered 2: is the consolidation model month number for the row/column we are in = the month number on which we should start including the submodel These conditions do not apply to the first model as it is always included. My formula which appears below has worked fine for three workbooks, but now I am asked to include a total of 8 workbooks making the formula cumbersome and likely (haven't tested yet) running over the formula character limit. All the sheets are formatted the same with month numbers running on line 3 and account names in column B. * * * *1 2 3 .. 120 Accnt 1 Accnt 2 Accnt 3 ... Acct 65 =IF(Model1_Flag="y",VLOOKUP($B6,'Model1.xls'!PLDat aTable,MATCH(M $3,'[Model1.xls]PandL'!$B$3:$EB$3,0),FALSE),0) +IF(AND(Model2_Flag="y",M$3=Model2Start),VLOOKUP( $B6,'Model2'! PLDataTable,MATCH(M$3-Model2Start+1,'[Model2.xls]PandL'!$B$3:$EB$3), 0),FALSE) +IF(AND(Model3_Flag="y",M$3=Model3Start),VLOOKUP( $B6,'Model3.xls'! PLDataTable,MATCH(M$3-Model3Start+1,Model3.xls]PandL'!$B$3:$EB $3,0),FALSE)) *I would appreciate any feedback on how to change the formula, us VBA instead, et Thank you, that is helpful. I don't know where the "yes" button is, but I will give a positive rating to your reply. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If isna match vlookup formula | Excel Worksheet Functions | |||
Formula To Use A Dynamic Named Ranges With VLOOKUP On Different Workbooks | Excel Worksheet Functions | |||
n/a in vlookup/index/match formula | Excel Worksheet Functions | |||
dynamic, double vlookup, match, index, dget?? different workbooks | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |