Adding totals by the week

[avixion]

Hello,

I am trying to find a way to do a couple of things. 1st (in worksheet 1) in column K, L M and N, it is calculating the numbers of YES and NO. What I am looking for is to move the totals based on the weeks in dates in column B and move the totals to (worksheet 2, tab WA) and put the totals by the week. for example is the date is between Monday January 29, 2024 to February 4, 2024, it will put that total in that week and so on.

Please see the attached worksheets 1 and 2. I hope this makes sense.

thanks,
Avixion

[HansV]

It's unfortunate that 1.xlsx is organized by month and 2.xlsm by week. For example, the week of 29th January, 2024 can have data in the January sheet and in the February sheet.
Can't you move all data in 1.xlsx to a single sheet for the entire year?

[avixion]

That is true that end of January data may fall in a little bit in February and same with other months as well. As far as merging two sheets together is unfortunately difficult because sheet 1 will be updated by each individual departments and sheet 2 will be housed in a separate folder pulling data from all those sheets in one place for auditing.

[avixion]

If we can use column A (Date Funded) in sheet 1 instead of column B, which means it will not fall into next month, is there a way to make it work?

[avixion]

Also, we can only collect totals from columns K and L in sheet 1 and not use columns M and N and move those totals in sheet 2 by weeks.

[HansV]

I'll have a look.

[HansV]

I'm sorry, i have looked at it, but it's too complicated with this layout.

[avixion]

Thank you for checking Hans. I appreciate it.

[p45cal]

Are the columns K:N in the January sheet of workbook1.xlsx derived from the table immediately to the left?
If so it's actually easier to query 1.xlsx from 2.xlsx than it is to query 1.xlsx from itself.
If not, could those columns be included in that table?

From a little exploration, I was quite easily able to get this into 2.xlsm by querying 1.xlsx:

2024-01-31_020410.jpg

(I've hidden weeks with no data)

or like this:

2024-01-31_021713.jpg