Hi all, not sure this is even possible reading through the forums, but I'll give it a go and ask the question, or have to look at some sort of vb script.
I have a root folder called "Teachers" and under that folder, hundreds of subfolders for all the schools ( e.g. Teachers\School 1\; Teachers\School 2\; Teachers\School 3\; etc..) and within each of those folders are excel files with a name that has a date, "Report 2020 10 01.xlsx";"Report 2020 11 01.xlsx";"Report 2020 11 30.xlsx".
I'm currently using the formula on my consolidated spreadsheet that is '\\Teacher\School 1\[Report 2020 11 01.xlsx]Summary'!$D$85 to pull the specific number from that cell in that file into my consolidated spreadsheet, repeating hundreds of times for each school.
Is there a way to use a cell reference in my formula so I change the date on my consolidated form and it uses that to pull the information?
CONSOLIDATED SPREADSHEET
I have a root folder called "Teachers" and under that folder, hundreds of subfolders for all the schools ( e.g. Teachers\School 1\; Teachers\School 2\; Teachers\School 3\; etc..) and within each of those folders are excel files with a name that has a date, "Report 2020 10 01.xlsx";"Report 2020 11 01.xlsx";"Report 2020 11 30.xlsx".
I'm currently using the formula on my consolidated spreadsheet that is '\\Teacher\School 1\[Report 2020 11 01.xlsx]Summary'!$D$85 to pull the specific number from that cell in that file into my consolidated spreadsheet, repeating hundreds of times for each school.
Is there a way to use a cell reference in my formula so I change the date on my consolidated form and it uses that to pull the information?
CONSOLIDATED SPREADSHEET
A | B | |
---|---|---|
1 | Date to use in the formula | 2020 11 30 |
2 | Formula | '\\Teacher\School 1\[Report B1 Date.xlsx]Summary'!$D$85 |