I just started a new job and a report assigned to me to update has arrays. I am taking the excel expert course and reviewed arrays so I now understand what the formulas are trying to do (well, I mostly understand) but I wonder if there is a more efficient way to do it.
The workbook has multiple tabs, 1 for each cost center. Department P&L statements are pasted onto each tab, with the tabs titled the cost center number. A summary page in the front has the array formulas pulling data from the P&L's.
'{=IF(B$3="",0,SUM((INDIRECT(B$3&"!$a$10:$a$68")=$A13)*INDIRECT(B$3&"!$f$10:$f$68")))+IF(B$4="",0,SUM((INDIRECT(B$4&"!$a$10:$a$68")=$A13)*INDIRECT(B$4&"!$f$10:$f$68")))+IF(B$5="",0,SUM((INDIRECT(B$5&"!$a$10:$a$68")=$A13)*INDIRECT(B$5&"!$f$10:$f$68")))+IF(B$6="",0,SUM((INDIRECT(B$6&"!$a$10:$a$68")=$A13)*INDIRECT(B$6&"!$f$10:$f$68")))+IF(B$7="",0,SUM((INDIRECT(B$7&"!$a$10:$a$68")=$A13)*INDIRECT(B$7&"!$f$10:$f$68")))+IF(B$9="",0,SUM((INDIRECT(B$9&"!$a$10:$a$68")=$A13)*INDIRECT(B$9&"!$f$10:$f$68")))}.
This formula is copied across 6 columns, which are the 6 departments for the report.
B$3-B$7 are the cost center numbers that are in each column and are the cost centers that make up that department. We need to sum multiple ones (for example, sum the cost center in B3 and in B4) because combined they make up the Technology department.
A13 is the description on the P&L we want to pull (for example, salary).
Rows 10-68 are fixed and used for the array formula so that the formulas don't pull some of the ratios on the P&L. I often have to add rows so that the array formula doesn't look at those ratios when the department P&L is shorter than the other department P&Ls.
I feel there has to be a better way to do this but I am lost as to what it could be....
Hi Erica,
Welcome to the forum. BTW I've moved your post to the Excel Expert members forum.
There certainly is a better way to achieve what this array formula is doing. The solution is to fix the pooly laid out data so you can use the Excel tools the way they were intended. Unfortunately if the data is spread over multiple sheets there won't be much you can do to improve that formula.
A while ago I wrote a post on why you should avoid overly complex formulas. You can should read it: https://www.myonlinetraininghub.com/avoid-writing-complex-excel-formulas
It sounds like the workbook is being created in the opposite order to what it should be. That is, the reports are being created (on individual sheets) and then you're trying to summarise the data. What should happen is the data should be collated in one tabular data set from which you summarise and generate reports.
If the P&L data is being pasted into individual sheets then the solution is to paste it into one sheet so it can be easily summarised (however, it may need reformatting to get it in a tabular layout). You can then generate the individual report tabs using PivotTables.
I would find out where the data comes from and see if you can get it from the system in a tabular format. That way you can use PivotTables to generate the summary data. You may also be able to generate the individual P&L reports from the source system as opposed to using PivotTables for the individual tabs.
I hope that gives you some ideas and points you in the right direction. I suspect you will have further questions, so I'm here if you need me.
Kind regards,
Mynda
Thank you. I know the accounting group provides the data straight out of their system but I do not know if the system puts it into the individual tabs or if they do that. To do it tabular, I would have quite a bit of headings to remove from each one that I believe are autogenerated to be at the top of each page (there are multiple pages in each tab). I will see what I can find out. Thanks for the advice on where to start
It sounds like Power Query will be your saviour.You can learn more about what Power Query is and how it can help you on my course page here:
https://www.myonlinetraininghub.com/excel-power-query-course
You can see an example of how Power Query transofmed a report with loads of headings here: www.myonlinetraininghub.com/reformat-excel-reports-with-power-query
The great thing about Power Query is you can set it up to clean and consolidate the reports on all of your tabs, and you can have it repeat the task at the click of a button each time the reports are updated.
That said, the accounting system will definitely have the data in a tabular format. That's how all databases store data. You just have to ask the database administrator to get you the data in the format you need, instead of the unhelpful 'reports' they currently provide.
Mynda
