Long calculation time on opening of workbook

[AllyGraham]

I have a personal spreadsheet that I use for logging all sorts of cycle ride related data. It is a large spreadsheet that does have a lot of formulas in it. I recently undertook a project to take what I have learnt over the last few years to improve the efficiency of formulas; such as using INDEX to define ranges based on MATCH results for COUNTIFS, SUMIFS, and so on, rather than performing such over a whole column(s). I feel this has worked well.
Where I think I might have gone backwards is using named ranges. I have repeat INDEX, VLOOKUP, etc. that refer to a fairly static range, so I used a named range and then INDEX to refer to a column within that range. This might be used as much as 100K times in the workbook, and I thought it was all going well. One of the main reasons I used a named range is to reduce the time it takes to insert a new row into the range and have Excel update the range in those 100K entries, as I had thought it would make it quicker to perform such a function (before re-calculating) as the named range reference is the only aspect that would be updated and once. I do not think that has worked the way I had hoped, as it still seems to take just as long to insert new rows in manual calculation method.

Where I am at now, and before I start going down a rabbit hole to either roll back the changes I have made or try a different version of what I was attempting, as well as seeing other improvements I could make to further reduce the re-calculation upon new data entry as part of my day-to-day use of the workbook... I have an issue that I wished to ask for advice on:
Now when I open the workbook, before I have access to the data within (sheets are not even listed at the bottom), there is 5-10 minutes of calculating being performed (mainly being displayed in the 90 percents). I had let it run through, made no changes, saved, and re-opened for it to start all over again. There are Visual Basic elements, but none that are on the workbook open, closed, or save events. I eliminated volatile formulas a long time ago in this workbook.
So my question(s), considering it has been ok until now:
a) is it possible that the named ranges (within the same workbook) cause this re-calculation each time the workbook is opened?
b) outside of VBA (which is not being used at this point or upon closing and saving) what could be volitine upon opining of the workbook each time?
c) or is it the new 'dynamic' ranges using INDEX that are the cause of a re-calculation even though there has been no changes to anything since closing and re-opening?

Any thoughts greatly appreciated.
And yes, I am going to try and eliminate formulas as much as possible, and I have been over the years of this workbook, but there are large elements that can be dynamic at any given time based on new data I add.

 

[RoryA]

If you use index as one (or both) argument to a range defined using a colon - eg :A1:INDEX(A:A,somenumber) or an INDEX():INDEX() construction - then it will recalculate on opening the workbook.

 

[AllyGraham]

If you use index as one (or both) argument to a range defined using a colon - eg :A1:INDEX(A:A,somenumber) or an INDEX():INDEX() construction - then it will recalculate on opening the workbook.

Thank you Rory, that will be the reason then. I will work on that when I get home. Any suggestions from experience to next best option? I am thinking that I can add another helper column to use as a reference for the INDEX column and/or row number, as this will avoid the use of the INDEX as an argument as you have described.

EDIT: scrap that idea I had, it would not work like that, as it would still require the argument as before.

 

[RoryA]

You may be better off using a Table as the source data (since those are effectively dynamic ranges). It also sounds like you might benefit from using pivot tables for your summary data rather than COUNTIFS/SUMIFS formulas etc.