# Non-volatile substitute for INDIRECT function

#### AndyFarrell79

##### New Member
Hi All,

I'm currently working on a very large file and excel is consistently crashing on me. I believe this is largely due to the number of indirect functions I'm using. I have 31 sheets, named D1 - D31, for each day of the month. I then have a data sheet and 6 sheets analysing the monthly data. These 6 sheets are where the INDIRECT functions are.

Here is an example of a SUMIF formula that I need to extract from ranges in the monthly sheets: (Cell K3 provides the number from the date selected by the user)

=SUMIFS(INDIRECT("'D"&\$K\$3&"'!\$C\$290:\$C\$1401"),INDIRECT("'D"&\$K\$3&"'!\$B\$290:\$B\$1401"),"Total",INDIRECT("'D"&\$K\$3&"'!\$AE\$290:\$AE\$1401"),"DT")

Does anyone know of a way I can get this data with non-volatile functions? This is just one (and not the most complex) of many INDIRECT formula in these sheets.

Thanks,

Andy​

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### ukmikeb

##### Well-known Member
Hi

Welcome to the MrExcel Forum.

You could use a Worksheet_Change event focused on cell K3 to insert the qualified formulas in the sheets.

That way you would remove the INDIRECT.

hth

##### MrExcel MVP
What is the reason for invoking INDIRECT here for "'D"&\$K\$3 seems fixed, not copiable?

#### AndyFarrell79

##### New Member
ukmikeb - cheers I'll have a look into the Worksheet_Change

Aladin - The cell is chanxged by the user to select the date and hence worksheet that they wish to analyse.

Another question someone may know off the top of their heads...

Which is more efficient?

Smaller formulas in multiple cells and then references to these cells in the final cell to calculate

or

One cell with a larger formula doing all necessary calculations itself

Thanks to all those helping me with this

Cheers,
Andy

##### MrExcel MVP

ukmikeb - cheers I'll have a look into the Worksheet_Change

Aladin - The cell is chanxged by the user to select the date and hence worksheet that they wish to analyse.

Just this formula won't cause terrible efficiency problems. But volatile functions tend to degrade performance. Try to replace the choice implying indirect with choose.

Another question someone may know off the top of their heads...

Which is more efficient?

Smaller formulas in multiple cells and then references to these cells in the final cell to calculate

or

One cell with a larger formula doing all necessary calculations itself

Thanks to all those helping me with this

Cheers,
Andy

More often than not the smaller formulas.

See decisionmodels.com for efficiency issues.

#### tusharm

##### MrExcel MVP
Use the CHOOSE function. For example, =SUMIFS(CHOOSE(A1,'D1'!A1:A2,'D2'!A1:A2,'D3'!A1:A2,'D4'!A1:A2,'D5'!A1:A2,'D6'!A1:A2),
CHOOSE(A1,'D1'!B1:B2,'D2'!B1:B2,'D3'!B1:B2,'D4'!B1:B2,'D5'!B1:B2,'D6'!B1:B2),"a").

In my test A1 contained the day-of-month, i.e., K3 in your example.

Hi All,

I'm currently working on a very large file and excel is consistently crashing on me. I believe this is largely due to the number of indirect functions I'm using. I have 31 sheets, named D1 - D31, for each day of the month. I then have a data sheet and 6 sheets analysing the monthly data. These 6 sheets are where the INDIRECT functions are.

Here is an example of a SUMIF formula that I need to extract from ranges in the monthly sheets: (Cell K3 provides the number from the date selected by the user)

=SUMIFS(INDIRECT("'D"&\$K\$3&"'!\$C\$290:\$C\$1401"),INDIRECT("'D"&\$K\$3&"'!\$B\$290:\$B\$1401"),"Total",INDIRECT("'D"&\$K\$3&"'!\$AE\$290:\$AE\$1401"),"DT")

Does anyone know of a way I can get this data with non-volatile functions? This is just one (and not the most complex) of many INDIRECT formula in these sheets.

Thanks,

Andy​

#### ukmikeb

##### Well-known Member

Hi

If the cause of Excel crashing was due to each formula having to resolve the sheet address.

Might it be possible to workaround this by building these addresses in separate cells, say AA1, AB1 and AC1, like :-
Code:
``````AA1 - ="'D"&\$K\$3&"'!\$C\$290:\$C\$1401"
AB1 - ="'D"&\$K\$3&"'!\$B\$290:\$B\$1401"
AC1 - ="'D"&\$K\$3&"'!\$AE\$290:\$AE\$1401"``````
and then the formula in your OP becomes -
Code:
``=SUMIFS(INDIRECT(AA1),INDIRECT(AB1),"Total",INDIRECT(AC1),"DT")``

hth

Replies
1
Views
76
Replies
7
Views
401
Replies
2
Views
611
Replies
1
Views
558
Replies
3
Views
169

1,127,093
Messages
5,622,666
Members
415,917
Latest member
kungsleden

### 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?

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