INDIRECT function slowing down workbook - alternatives?

[victorjensen]

Hi.

I have a workbook with around 25 sheets. Some of the sheets are data consisting of only values which we import from our financial system, which means its a bunch of rows with an account or employee number and the corresponding expenditure. In the other sheets we sort all of the data into rapports and usefull overviews. This is done with the use of VLOOKUP and SUM.IFIS functions primarily, but we also use the INDIRECT function since we need to update it every month with 5 new datasheets which now will be the new references in all the SUM.IFIS functions.

My problem is that the workbook is beginning to be really slow. When you press enter to calculate a formula it takes around 5-10 sec for it to finish.

Most of the formulas are like this:
=SUM.IFS(INDIRECT($Z$2&"!G:G");INDIRECT($Z$2&"!A:A");A334)

Where,
Z2 = A cell with the name of the sheet, could be: Salaries01
A:A = The column with employee number in data sheet
G:G = The column with salary for that given employee

Since this saves me time from having to edit all the formulas every single month by being able to change the text in cell Z2 to the name of the new sheet, i would really appreciate if anyone knows of an alternative.
I would prefere not to use VBA since i am not proficient in it. If that is the only solution i am open for the idea, but would need a quite thorough walk through..

Thank you in advance!

 

[sandy666]

I don't know the structure of your workbook but maybe Power Query (Get&Transform) will be a solution

Power Query documentation - Power Query

Power Query is used to filter, combine, and mash up data from one or more supported data sources.

docs.microsoft.com