I am trying to build a Summary Sheet
, which imports data from Project Sheet
s as they are created and updated. The Summary Sheet
is stored in a team drive, and the Project Sheet
s are stored in a separate folder in that team drive. Each new project will have its own sheet, which is created when the project is accepted according to a template.
The folder for Project Sheet
s will end up with many sheets, for example:
01112016
02112016
15112016
12122016
The Summary Sheet
then needs to IMPORTRANGE data from these sheets as they are created and updated.
SummarySheet
Project 01112016, Date1, Date2, Sum1, Average1
Project 02112016, Date1, Date2, empty, empty
Project 15112016, Date1, empty, empty, empty
Project 12122016, empty, empty, empty, empty
empty
I had tried building this in Excel, but the indirect function doesn't pull data from closed workbooks. In Google Sheets I have a different problem, the addresses of future sheets are unknown, because each new sheet address is unpredictable (as far as I know). There may be a thousand project sheets created each year, so automating the Summary Sheet
is important.
Any ideas how to predict new sheet addresses, or to identify sheets in the folder of a team drive, in order to link Project Sheet
s and Summary Sheet
?