Create Excel spreadsheet with index of folder content

[jmak]

Edit: I think I solved this myself. I am going to try using =CELL("filename") to find the path and add the result into my formula.

 

I'm trying to create a learning log for a course. They'll gather evidence of having completed tasks in documents that they'll be given templates for. As participants don't belong to a single organisation and the course administrators don't have a cloud computing tenancy, the intention is to set up a folder structure with all of the required documents and send it as a zip file to the participants to update.

 

I'd like to include a file which lists the content of the folder and updates on demand. This will essentially be a tracking document so that the trainers can see what work has been completed and what is outstanding.

 

I can see how to do this with Excel using the File function, but for that you need the whole path. I won't know that when I set up the folder as presumably it will include the username and may not be in the c:users directory. Can I use a wildcard in the formula or is there a way to automatically pull in the path? Or is there a better way of doing it?

 

I hope that most participants will have Office of some flavour, but can't guarantee what one. The long-term solution is a Learning Management System - but despite being very expensive and not offering anything that M365 doesn't, it's running years behind...

Edited March 29, 2021 by jmak
I think I solved it myself