I am working on my company project to calculate average of daily production efficiency maintain in excel template. Each day create new file with the day and enters the data and calculate the efficiency using same template.
The excel file has six sheets call DAY-Line 1, DAY-Line 2, DAY-Line 3 and there are over 300 files with data on it. We need to calculate average efficiency in the past and bring it over to the template to update the efficiency automatically. I found this can be done writing formula as below
=AVERAGE('[Run Rate Chart - 20151201 Tuesday.xlsx]Day - Line 1:Day - Line 3'!$I$52:$I$84) this works fine for each file since I have 300 files it is very time consuming go to each files names to write this formula.
I got all the names writing formula if I can write some kind of concatenation formula I can get the average dynamically. I tried to write formula
=average (indirect("'"&"[Run Rate Chart - 20151201 Tuesday.xlsx]Day - Line 1:Day - Line 3'!$I$52:$I$84")) to first test whether I can use indirect function to use for the task, but unfortunately it gets #ref error.
Can any one help me to write correct formula for this situation.
Thank you
The excel file has six sheets call DAY-Line 1, DAY-Line 2, DAY-Line 3 and there are over 300 files with data on it. We need to calculate average efficiency in the past and bring it over to the template to update the efficiency automatically. I found this can be done writing formula as below
=AVERAGE('[Run Rate Chart - 20151201 Tuesday.xlsx]Day - Line 1:Day - Line 3'!$I$52:$I$84) this works fine for each file since I have 300 files it is very time consuming go to each files names to write this formula.
I got all the names writing formula if I can write some kind of concatenation formula I can get the average dynamically. I tried to write formula
=average (indirect("'"&"[Run Rate Chart - 20151201 Tuesday.xlsx]Day - Line 1:Day - Line 3'!$I$52:$I$84")) to first test whether I can use indirect function to use for the task, but unfortunately it gets #ref error.
Can any one help me to write correct formula for this situation.
Thank you
