Offset vs Index - For Dynamic Named Range

[ttray33y]

Hi fellow members, I am lost.

I have a very large data in a spreadsheet everything is working, I started it from a cell reference "no named ranges" then revised it to have named ranges for my list but since OFFSET is a volatile function the calculation where too slow every time I enter data anywhere in the workbook, so I decided to convert it to Index it was fast only if you enter data on cell with no precedents and dependents formula's and it was slow at Open. The end part is I brought it back to cell references it is alot faster but the only downside is the file size. So to wrapped this down.

As list:
1.) Cell References - a lot faster but has a bloated file size, +4MB
2.) Offset - Fast opening, keeps recalculating every time you enter data anywhere.
3.) Index - No, recalculation anywhere, Slow at Opening and calculation.

 

[ttray33y]

You use them the same way as before. Nothing changes in that respect. Have you implemented the defintions as stipulated?



The formulas will work immediately as before.

By the way, why invoke this SUMIFS formula twice? ​

Keep just this...

=SUMIFS(PCALPG,PCALREV,"IFR-1",PCALDOC,$A$1)

and custom format the cell of the formula as:

​

[=0]"NO BUDGET ALLOCATION";General

WOW that was awesome, so much lessons here, now with index running faster + smaller file size. brilliant Aladin, you're indeed a magician.
1 problem though, it still opening so slow, maybe around 30-1min.

 

[Aladin Akyurek]

You mean 30 secs to 1 min, right?

Do you have formulas with OFFSET or INDIRECT in this workbook?

 

[ttray33y]

You mean 30 secs to 1 min, right?

Do you have formulas with OFFSET or INDIRECT in this workbook?

no i dont have any formulas with offset or indirect, just sumif and sumifs

 

[Aladin Akyurek]

no i dont have any formulas with offset or indirect, just sumif and sumifs

Strange. Try to reduce to bare bones all sum(s) formulas which look like the one we discussed earlier.