Providing a list of files in Excel of a directory is slowing down excel

[papa8]

I need to provide a current list of files in a directory in an Excel workbook and everything is working as required, just too slowly. I really only need the list to check it is current once upon opening the workbook. It takes around 11 seconds to do this which is acceptable but the problem is it keeps rechecking this every time I carry out even minor edits to the workbook (I guess due to the fact that it is brought in as an Excel table). I determined the lag in my workbook using the rangetimer() function that is provided and it is the only thing taking a long time to calculate. I should also state that the table containing the list of files is finally used in a cell on another worksheet to provide a data validation drop-down list but don't believe this is really the issue.
I did some Googling on reducing Excel calculation times and discovered that there are some Excel functions that are definitely culprits for increasing calculation times (described as volatile) and three of these (NOW,INDEX and ROW) are used in providing the functionality I would like in this part of the workbook.
I have tried two solutions so far: 1. Force Full Calculation set to True in VBA properties window 2. Switched calculations to manual. I set this back to automatic once I identified that this part of the workbook was the issue as I don't want manual calculation generally.
The formula I have in the 'refers to' box of the named range (TutorFileList) is:

=FILES("\O008DC01\Shared\Tutor Qualifications*")&T(NOW())

The formula I have in each cell of the excel table is:

=IFERROR(INDEX(TutorFileList,ROW()-1),"")[/FONT][/COLOR]

What I would like to have is the ~11secs of calculated time to find these files reduced down to just one check of the networked directory rather than it taking 11secs of automatic recalculation every time the workbook is modified.
If there is a more efficient way to achieve what I am doing I am prepared to redesign things but I do need the functionality of a drop-down list of files in the specific directory in a cell.
Many thanks for assistance from anyone on this.

I should state I have used the cell formula in 500 rows (which is a little above the maximum I expect the list to grow to). Also Excel 2016 is what I am using and what I created the workbook in. Another staff member who is inputting into this on Excel 2010 does not have such a lag so perhaps it is an issue more with later versions of Excel.

• 
  

 

[papa8]

I have resolved the issue myself but if it ever interests a future person searching for this kind of problem here is what I did. The calculation lag is drastically reduced if I reduce the number of cells from 500 uses of the index formula to say 250. If I want more displayed in the future I can increase it with dragging the fill handle down (and admittedly have to deal potentially with the lag again).