Using the Sum Function and Day Function in the same Formula

[shawna_mese]

I am trying to add the Day function into a Sum formula to make updating each sheet easier on a report.

The formula is:

=SUM('F:\Daily Reports\Jackson Lake\Jackson Lake Daily Plant Reports 2021\[Jackson Daily Report - 2021 10 October.xlsx]11'!$G$14)

I would like to use the Day function to automatically pull the day from the same sheet that the formula is in.  Otherwise, I have to manually change the formula to pull the correct day.

=SUM('F:\Daily Reports\Jackson Lake\Jackson Lake Daily Plant Reports 2021\[Jackson Daily Report - 2021 10 October.xlsx],DAY(A2)'!$G$14)

Any help on this would be greatly appreciated, as it would save a lot of time updating each spreadsheet for the week.

Respectfully,

Shawna Mese

[#2]

@shawna_mese Your screenshot suggest that there's more than just linking to daily reports in a monthly folder. It shows "7 day average" and "30 day average", meaning that you need to keep track of previous 30 days at any given date as well. My guess is that you have one sheet for every week.

What you describe could be done with the INDIRECT function, but all daily reports would have to be open. And  that is probably not going to happen. I believe that this is something that should be handeld with PowerQuery (PQ), if you are not on a Mac, that is!

With PQ you can connect to all files in one folder and extract data from each file in that folder. You would build a single table looking like the one in your screenshot (without the totals at the bottom) and do the weekly and 30-day analysis from there. Add a new daily report to the specified folder, refresh the PQ generated table and all relevant data for the new day is included.

It needs to be set-up properly once for it to work over and over again, without the need of complicated formulae. Exactly how? That depends on the structure of the daily reports.

The link below is a good starting point to learn more about PQ. Chapter 9 in particular deals with importing files from a folder.

https://exceloffthegrid.com/power-query-introduction/ 

[#3]

@shawna_mese 

Sinceresly the best approach is by Power Query as explained by @Riny_van_Eekelen 

In case you still need it by formula my suggestion is:

In some place define the full address of the workbook.

K1=F:\Daily Reports\Jackson Lake\Jackson Lake Daily Plant Reports 2021\[Jackson Daily Report - 2021 10 October.xlsx]

B2=INDIRECT($K$1&DAY(A2)&"'!$G$14")

As you are referencing just G14 its not necessary the SUM() formula.

[#4]

@Juliano-Petrukio Note this (from the support screen on INDIRECT):

I doubt that @shawna_mese will open all daily files, every time she needs to update the weekly reports. That's why I dismissed it as a feasible solution.

[#5]

I totally agree with you. That`s why I started posting about the best approach with PQ. The INDIRECT() option is barely applicable considering external references already in her workbook.

[#6]

Awesome! I'll try PQ.
Thank you for the information.