INDIRECT function slowing down workbook - alternatives?

victorjensen

New Member
Joined
Jan 8, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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!
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
I don't know the structure of your workbook but maybe Power Query (Get&Transform) will be a solution
 

Watch MrExcel Video

Forum statistics

Threads
1,122,529
Messages
5,596,694
Members
414,088
Latest member
rodriboraun

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top