I have a budget spreadsheet where:
-
I have a summary sheet called EXPENSES with cost totals organized by month, i.e. JAN, FEB, MAR etc. So I have the whole year view in one sheet.
-
The detailed expenses are organized in separate sheets by month, i.e. JAN, FEB, MAR etc. The costs in these sheets are organized in groups (i.e. Groceries, Travel [w trows for tickets, hotels, flight tickets etc.] and each group has several rows of items that sum up to a total for the group. The groups / ranges have been given names.
-
The detailed expenses in these ‘pick-up’ / data input sheets all have the same structure and the same named groups with the same name. So basically the FEB sheet is a one-to-one copy of the JAN sheet and so on. The only difference is the sheet name in the tab.
-
In the summary or target sheet with the INDEX formula (EXPENSES) I want to be able to search the named groups to pick up not only the total cost for the group, but also search for itemised cost within a group.
I am using an INDEX MATCH formula where it is told to go to the JAN pick-up / data input sheet (the INDIRECT formula) to pick up the cost data. So I want the formula in the target sheet cell (EXPENSES) to go to Row 5 of the column to see which month it is and then go to the the pick-up / data input sheet for that same month to get the data.
So if the formula is in the FEB column (as stated in cell E5), the INDIRECT formula should create a cell reference to go to the pick-up / data input sheet named ‘FEB’ to pick-up the cost from the specified group or group item
In Excel this formula does just that:
INDEX(INDIRECT(""&E$5&"!GroceriesGroupCost"),MATCH($EXPENSES.$D$57,INDIRECT(""&E$5&"!GroceriesGroup"),0))
where:
-
$E$5s is the cell in row 5 in the formula sheet (EXPENSES) where INDIRECT picks up the name of the month, i.e. FEB.
-
the INDIRECT formula should then use the name/text in cell E5 (FEB) to create the cell reference to the data inut sheet ‘FEB’. So basically create a referece to '$FEB.D63`
So it should take me to the FEB sheet to get the data from the named group GoceriesGroupCost, which is part of the group called GroceriesGroup.
I would like to use the dynamic reference capability in INDIRECT so that I do not have to enter the formula for each and every cell and repeat the process if subsequent changes are made.
Can anyone help me with the INDIRECT formula syntax.