Non-volatile substitute for INDIRECT function

[AndyFarrell79]

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]

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]

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

 

[AndyFarrell79]

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]

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]

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]

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 :-

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 -

=SUMIFS(INDIRECT(AA1),INDIRECT(AB1),"Total",INDIRECT(AC1),"DT")

hth

 

[ukmikeb]

Cross-posted - Non-volatile substitute for INDIRECT function

 

[AndyFarrell79]

Thank you all who have offered their help with this. I do appreciate your time and knowledge. I've decided to attack the problem from a different angle and the thread can be found at:

http://www.mrexcel.com/forum/excel-...-code-copy-data-new-workbook.html#post3756623

Thanks again,

A​