Hello,
We have a spreadsheet that is referencing data based on the criteria if it says 'Flat Cash' or 'Projects':
<tbody>
</tbody><colgroup><col><col><col span="12"></colgroup>
This is the formula in the cell C5 for the value: 35,388:
=IF(VLOOKUP($A$35,'INPUT DATA'!$D$39:$E$46,2,FALSE)=Analysis!$B51,Analysis!C$35,0)+IF(VLOOKUP($A$36,'INPUT DATA'!$D$39:$E$46,2,FALSE)=Analysis!$B51,Analysis!C$36,0)+IF(VLOOKUP($A$37,'INPUT DATA'!$D$39:$E$46,2,FALSE)=Analysis!$B51,Analysis!C$37,0)+IF(VLOOKUP($A$38,'INPUT DATA'!$D$39:$E$46,2,FALSE)=Analysis!$B51,Analysis!C$38,0)+IF(VLOOKUP($A$39,'INPUT DATA'!$D$39:$E$46,2,FALSE)=Analysis!$B51,Analysis!C$39,0)+IF(VLOOKUP($A$40,'INPUT DATA'!$D$39:$E$46,2,FALSE)=Analysis!$B51,Analysis!C$40,0)+IF(VLOOKUP($A$41,'INPUT DATA'!$D$39:$E$46,2,FALSE)=Analysis!$B51,Analysis!C$41,0)+IF(VLOOKUP($A$42,'INPUT DATA'!$D$39:$E$46,2,FALSE)=Analysis!$B51,Analysis!C$42,0)
In the Input Data tab we have a list of codes to lookup to see if they are noted as 'Flat cash' or 'projects'
<colgroup><col width="64" style="width: 48pt;" span="2">
<tbody>
</tbody>if the numbers are flat cash I need them to total up the amount in the next table and give me the total number, same for projects. this is the table where the numbers are pulling from:
<tbody>
</tbody><colgroup><col><col><col span="12"></colgroup>
As you can see from the above vlookup formula it is far too long. Do you have any suggestions of how this could be condensed and made more robust?
Thank you,
Peggy
We have a spreadsheet that is referencing data based on the criteria if it says 'Flat Cash' or 'Projects':
P5B | IS Facilities | ||||||||||||
FINANCIAL FORECASTING 2016/17 | |||||||||||||
Analysis by Flat Cash / Project | Actual | Actual | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | |
Net Expenditure | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | |
Flat Cash | 35,388 | 34,768 | 34,395 | 34,395 | 34,395 | 34,395 | 40,944 | 34,393 | 34,396 | 34,397 | 34,397 | 34,397 | |
Projects | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | |
Net Expenditure | 35,388 | 34,768 | 34,395 | 34,395 | 34,395 | 34,395 | 40,944 | 34,393 | 34,396 | 34,397 | 34,397 | 34,397 |
<tbody>
</tbody><colgroup><col><col><col span="12"></colgroup>
This is the formula in the cell C5 for the value: 35,388:
=IF(VLOOKUP($A$35,'INPUT DATA'!$D$39:$E$46,2,FALSE)=Analysis!$B51,Analysis!C$35,0)+IF(VLOOKUP($A$36,'INPUT DATA'!$D$39:$E$46,2,FALSE)=Analysis!$B51,Analysis!C$36,0)+IF(VLOOKUP($A$37,'INPUT DATA'!$D$39:$E$46,2,FALSE)=Analysis!$B51,Analysis!C$37,0)+IF(VLOOKUP($A$38,'INPUT DATA'!$D$39:$E$46,2,FALSE)=Analysis!$B51,Analysis!C$38,0)+IF(VLOOKUP($A$39,'INPUT DATA'!$D$39:$E$46,2,FALSE)=Analysis!$B51,Analysis!C$39,0)+IF(VLOOKUP($A$40,'INPUT DATA'!$D$39:$E$46,2,FALSE)=Analysis!$B51,Analysis!C$40,0)+IF(VLOOKUP($A$41,'INPUT DATA'!$D$39:$E$46,2,FALSE)=Analysis!$B51,Analysis!C$41,0)+IF(VLOOKUP($A$42,'INPUT DATA'!$D$39:$E$46,2,FALSE)=Analysis!$B51,Analysis!C$42,0)
In the Input Data tab we have a list of codes to lookup to see if they are noted as 'Flat cash' or 'projects'
T1A000 | Flat Cash |
T8A000 | Flat Cash |
T1B510 | Flat Cash |
T1B540 | Flat Cash |
T1B542 | Flat Cash |
TSA001 | Flat Cash |
U9A000 | Flat Cash |
U9B573 | Flat Cash |
M9T000 | Projects |
<colgroup><col width="64" style="width: 48pt;" span="2">
<tbody>
</tbody>
P5B | IS Facilities | ||||||||||||
FINANCIAL FORECASTING 2016/17 | |||||||||||||
Analysis by Job Code | Actual | Actual | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | Forecast | |
Net Expenditure | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | |
T1A000 | GENERAL IS OPERATING COSTS | 3,939 | 988 | 1,187 | 1,187 | 1,187 | 1,187 | 1,186 | 1,186 | 1,189 | 1,189 | 1,189 | 1,189 |
T8A000 | GENERAL SERVICE PROVISION | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
T1B510 | FLEET VEHICLES | 337 | 126 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
T1B540 | ML SECURITY | 0 | 0 | 0 | 0 | 0 | 0 | 5,500 | 0 | 0 | 0 | 0 | 0 |
T1B542 | OTHER SITE SECURITY | 0 | 0 | 0 | 0 | 0 | 0 | 750 | 0 | 0 | 0 | 0 | 0 |
TSA001 | IS SALARY CHARGES | 31,112 | 33,654 | 33,208 | 33,208 | 33,208 | 33,208 | 33,208 | 33,207 | 33,207 | 33,208 | 33,208 | 33,208 |
U9A000 | GENERAL COMPUTING EQUIPMENT | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
U9B573 | OCCUPATIONAL HEALTH EQUIPMENT | 0 | 0 | 0 | 0 | 0 | 0 | 300 | 0 | 0 | 0 | 0 | 0 |
Net Expenditure | 35,388 | 34,768 | 34,395 | 34,395 | 34,395 | 34,395 | 40,944 | 34,393 | 34,396 | 34,397 | 34,397 | 34,397 |
<tbody>
</tbody><colgroup><col><col><col span="12"></colgroup>
As you can see from the above vlookup formula it is far too long. Do you have any suggestions of how this could be condensed and made more robust?
Thank you,
Peggy