Named file folder range live updated and sorted

[Retroshift]

Hi,

I have a listFiles named range to return a list of files of a specific folder into Excel. I use the following formula:

=IFERROR(INDEX(listFiles,ROW()-ROW(A$2)),"")

I was wondering if there is a non-VBA solution to automatically/live update the files list in the Excel named range whenever a file in the folder gets added, deleted or modified.
Moreover, is there an Excel formula to alphanumerically sort these files dynamically/live?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also how have you defined ListFiles?

 

[Retroshift]

Updated. I use the Windows platform and Excel 2019.

I defined listFiles:

=FILES(Sheet1!$A$1)

 

[Fluff]

Ok, in Sheet1!A1 you could use

=IF(NOW(),"C:\Mrexcel\*")

this will mean the list will update anytime the workbook recalculates.

 

[Retroshift]

Ok, in Sheet1!A1 you could use

=IF(NOW(),"C:\Mrexcel\*")

this will mean the list will update anytime the workbook recalculates.

Thanks, but I was wondering if the workbook could recalculate by itself without having to press F9?
And also, if it could live sort the list alphanumerically?

 

[Fluff]

The list is sorted alphabetically for me, regardless of how the files are sorted in the folder.

The workbook can recalculate itself, but as the formula in A1 is volatile, the list will update whenever any cell in any open workbook is changed, or recalcs.

 

[Retroshift]

- I don't know why but the list does not return sorted in my sheet, and I cannot sort it with the sort and filter function.

- Is there an option to autorecalculate by itself without updating any cell in a workbook, or without having to press F9?

 

[Fluff]

Is there an option to autorecalculate by itself

Not that I know of.