Sum Vlookup / Match Formula across 8 workbooks

[kurt]

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'!PLDataTable,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.

 

[ShaneDevenshire]

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.

 

[kurt]

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

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'!PLDataTable,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.