Problem with a variable on a Sum formula using Offset to count days of the month every Thu

[CariocaExcel]

Hello!

I have a worksheet where every tab needs to be a month of the year, and every Thursday I have to report certain sums of the columns from the tabs. After some trial and errors I managed to get a formula to do that, but it had a minor setback: the weeks between months donīt count the previous tabs.

So here I am trying to fix that issue, I have considered using SUM and OFFSET, COUNTA and even INDEX to get it right; but I am not being able to have it done what I need.

The attachment contains a small draft of the worksheets and its tabs and here is briefing on the columns formulas and what was supposed to do:
Columns N, P and E will calculate the last 7 days of operations, if it is a Wednesday. The formula is:

Please Login or Register  to view this content.

I used the months of July, August and September as example, on the Sep2019 tab the value on cell N8 should return the 4 days prior September 5th and should also calculate the 3 days from the Aug2019 tab. Here is the point I canīt seem to build the formula correctly, since I have to deal with months that ends on 28, 29 (Leap Years), 30 or 31.

Hope someone can help me with this conundrum!

Cheers,
Diogo Cuba

[WideBoyDixon]

Try these formulas in N5:S5

Please Login or Register  to view this content.

Then copy the formula down from N5:S5 to N6:S35. That should help retrieve data from the previous sheets.

See attached.

WBD

[CariocaExcel]

Thanks for the input, WideBoyDixon.

I have downloaded the file and did some tests, unfortunately they are not counting the dates from 29-31AUG on the previous tab and is summing only the columns from the tab of September.

[WideBoyDixon]

The sheet I uploaded has 84 in N8 on the September tab; achieved by summing up the 1-4 September + 29-31 August.

WBD

[CariocaExcel]

The file I have downloaded shows 48 on N8, on the tab Sep2019.

This is the formula on it:

Please Login or Register  to view this content.

Any chance you can upload it again?

[WideBoyDixon]

I suspect that your regional settings mean that the months don't come out as English. If you hit Alt+F11 to go into the editor, click Ctrl+G to open the immediate window and type the following:

Please Login or Register  to view this content.

Then hit return, I'm guessing it doesn't say "Aug2019" as the answer.

WBD

[WideBoyDixon]

So I wrote a function instead:

Please Login or Register  to view this content.

Which I then used in, say, N5:

Please Login or Register  to view this content.

This hard-codes the month names and makes the formula a bit simpler. Take a look.

WBD

[CariocaExcel]

Indeed it returns "ago2019"...

[CariocaExcel]

So I wrote a function instead:

Please Login or Register  to view this content.

Which I then used in, say, N5:

Please Login or Register  to view this content.

This hard-codes the month names and makes the formula a bit simpler. Take a look.

WBD

Thatīs an amazing solution, man. I have just added a piece of code to the function to make it volatile: "Application.RefreshAll" since at first it seemed not to be working...

[CariocaExcel]

Actually I used:

Please Login or Register  to view this content.

[CariocaExcel]

So I wrote a function instead:
...
WBD

I appreciate a lot of your time and effort, man!

Can I bother you a little bit more to help undertand this piece of code?