Yes Sir: I have a workbook with several sheets, one of which is named 3DEXP and others named for fiscal years (FYE2006, FYE2007,...,FYE2016). Each of the FYE sheets contains financial data for the entire year by line item representing a fund, a department within that fund and the item (01-11-421 for instance mean general fund, admin department, full-time salaries; whereas 01-21-421 means the same thing but a different department, in this case the police department). The worksheet 3DEXP is being used to aggregate data from each fiscal year and the formula I am using does that perfectly:
=IFERROR(INDEX('FYE2006'!$D$4:$D$1322,MATCH('3DExp'!X$23,'FYE2006'!$B$4:$B$1322,0)),0)
In short, the formula is telling Excel to index everything in column D in the worksheet FYE2006, then go to column B and find the line item 01-11-421 or the line item 01-21-421 and return the amount matching the line item number (in this case 90,076 for 01-11 and 119,372 for 01-21). Here is a sample from the worksheet FYE2006, the sheets are similar across all the other years.
SALARIES-EMPLOYEES
| | | |
| 01-11-421
| SALARIES STAFF
| $90,075.72
|
| 01-11-422
| SALARIES PART TIME
| $0.00
|
| 01-11-423
| SALARIES-OVERTIME
| $1,691.22
|
| 01-11-428
| SALARIES ELECTED
| $5,959.96
|
| | | |
INSURANCE BENEFITS
| | | |
| 01-11-451
| HEALTH INSURANCE
| $29,687.85
|
| 01-11-452
| LIFE INSURANCE
| $2,591.32
|
| 01-11-453
| UNEMPLOYMENT INSURANCE
| $5,181.86
|
| 01-11-454
| DENTAL INSURANCE
| $6,232.20
|
| 01-11-459
| WORKER'S COMPENSATION INSURANCE
| $420.00
|
<tbody>
</tbody>
I was hoping to use Indirect to point to the correct worksheet so that when I copy down and across I do not have to go back and manually change the worksheet name for each successive year, because with the current formula that I am using, 'FYE2006'! will not auto change to 'FYE2007'! and so forth.
Here is a sample of aggregated data from the 3DEXP worksheet using the current formula and changing each year manually:
Salaries
| | |
Regular
| 01-11-421
| 01-21-421
|
FYE2006
| $ 90,076
| $ 119,372
|
FYE2007
| $ 84,708
| $ 116,168
|
FYE2008
| $ 88,628
| $ 112,527
|
FYE2009
| $ 66,994
| $ 123,617
|
FYE2010
| $ 89,083
| $ 82,665
|
FYE2011
| $ 91,513
| $ 120,695
|
FYE2012
| $ 90,525
| $ 95,818
|
FYE2013
| $ 93,798
| $ 146,800
|
FYE2014
| $ 32,298
| $ 166,213
|
FYE2015
| $ 32,850
| $ 139,422
|
FYE2016
| $ 32,850
| $ 139,422
|
<tbody>
</tbody>
Here is the formula changed to allow indexing and matching on the FYE2007 sheet: =IFERROR(INDEX('FYE2007'!$D$4:$D$1322,MATCH('3DExp'!X$23,'FYE2007'!$B$4:$B$1322,0)),0).
Everything is the same except that I changed the year from FYE2006 to FYE2007 and would have to do the same for each of the successive sheets.
