Non-volatile substitute for INDIRECT function

AndyFarrell79

New Member
Joined
Mar 17, 2014
Messages
7
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​
 

Some videos you may like

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
Joined
Jul 10, 2009
Messages
2,757
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
What is the reason for invoking INDIRECT here for "'D"&$K$3 seems fixed, not copiable?
 

AndyFarrell79

New Member
Joined
Mar 17, 2014
Messages
7
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203

ADVERTISEMENT

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
Joined
May 28, 2002
Messages
11,028
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
Joined
Jul 10, 2009
Messages
2,757

ADVERTISEMENT

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
 

Watch MrExcel Video

Forum statistics

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

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