Macro to Automatically select sum of "next" 7 cells

[bobwrong]

I have a workbook that I create a new sheet for every week. I then select the 7 previous days on a different sheet and have it add them together on the new week sheet I just created. example: SUM('Sales'!F4:F10)

I'm trying to create a macro that will select the "next" 7 days so I don't have to change that range every time I create a new week. ie. SUM('Sales'!F11:F17)

HELP!

And Thank you.

[6StringJazzer]

Welcome to the Forum!

Hard to say without seeing your data layout. Also would be helpful to see your current code. Is is possible to attach your file? That would save a lot of time.

To post a file, under the reply box click Go Advanced. Then in the next screen look above the reply box for the paper click icon. You will get a pop-up screen to select a file from your local drive, upload it, and then click Done to attach it.

[bobwrong]

Thanks for the welcome!

I can't attach the file itself but here is a dummy file that could be used to do the same. There are no current macros being used - I'm just starting the sheet (from somebody that was entering everything manually).

So with the file I'm uploading - sales numbers are entered daily in the "January Sales" - they are then automatically filled into the "Sales Sheet" worksheet for this year. What I want is for the WK 1, WK 2, WK 3 etc. worksheets to automatically pull the correct set of sales information from the "Sales Sheet" without me having to manually adjust the ranges every week when I create the next worksheet. Ideally I will have excel create the next weeks worksheet and auto-fill in most of the information on it's own. I hope this helps explain what I'm trying to do.

Thanks for the help.

[rylo]

Hi

From what I can see, there are only 3 cells on each sheet (B8, B20 and C20) that need to have formulas to bring back data from the sales sheet. Is this correct?

rylo

[bobwrong]

That is correct.

[rylo]

Hi

OK, B8 and B20 seem to want the same data so
=SUMPRODUCT(--('Sales Sheet'!A3:A33>='WK 2'!E2),--('Sales Sheet'!A3:A33<='WK 2'!G2),('Sales Sheet'!F3:F33))

C20: =SUMPRODUCT(--('Sales Sheet'!A3:A33>='WK 2'!E2),--('Sales Sheet'!A3:A33<='WK 2'!G2),('Sales Sheet'!B3:B33))

Note that this will not bring back any numbers from the test file as the data is for 2012, but the dates on the detail sheets are for 2013.

rylo

[bobwrong]

You are amazing! I can't believe I didn't ask here sooner. That is perfect. I had to change a couple things around as B8 and B20 are asking for different ranges but now that I know how it works it was simple!!! Thank you thank you thank you!!

Hi

OK, B8 and B20 seem to want the same data so
=SUMPRODUCT(--('Sales Sheet'!A3:A33>='WK 2'!E2),--('Sales Sheet'!A3:A33<='WK 2'!G2),('Sales Sheet'!F3:F33))

C20: =SUMPRODUCT(--('Sales Sheet'!A3:A33>='WK 2'!E2),--('Sales Sheet'!A3:A33<='WK 2'!G2),('Sales Sheet'!B3:B33))

Note that this will not bring back any numbers from the test file as the data is for 2012, but the dates on the detail sheets are for 2013.

rylo

[bobwrong]

Looks like I spoke too soon. When I place that formula in the "actual working" document it does not work. I have uploaded the actual document (or a version of it) so you can perhaps tell me what is causing it to not work. Thanks for all the help - the macros in this version are not needed and if they are causing problems can be removed.

[rylo]

Hi

The formulas in WK2 and WK3 for B8, B20 and C20 all seem to be the same. They don't seem to have any relationship to the dates that are in E2 and G2.

For those 2 sheets, exactly what range(s) from place Sales Sheet should be summed to get the correct result, and explain how that is achieved, what relationship (if any) they have to the nominated dates etc.

rylo

[bobwrong]

Hi!
That is simply because when I created WK 3 I just copied WK 2 as a template. I would usually go in and make the formulas match the dates in E2 and G2 so that the sales figures would be for the corresponding dates in either E2-G2 (for B20) J2-K2 (for C20) or B10-D10 (for B8). Sorry for that confusion - I hope my explanation helps a little.

Hi

The formulas in WK2 and WK3 for B8, B20 and C20 all seem to be the same. They don't seem to have any relationship to the dates that are in E2 and G2.

For those 2 sheets, exactly what range(s) from place Sales Sheet should be summed to get the correct result, and explain how that is achieved, what relationship (if any) they have to the nominated dates etc.

rylo

[rylo]

Hi

So in you example file, what should be the exact formulas for each of those 3 cells, for both the W2 and W2 sheets.

rylo

[bobwrong]

In the Dummy 2 File the formulas would be (If I was updated them manually):

WK2:
B8: =sum('place Sales Sheet'!F4:F10)
B20: =sum('place Sales Sheet'!F8:F14)
C20: =sum('place Sales Sheet'!B10:B16)

WK3:
B8: =sum('place Sales Sheet'!F11:F17)
B20: =sum('place Sales Sheet'!F15:F21)
C20: =sum('place Sales Sheet'!B17:B23)

Sorry for the delay in the response! Thanks for all your help!

[rylo]

HI

OK, going with the W2 sheet only and the formulas above, how do they relate to the dates on the sheet (E2, G2, J2 and K2). If they don't relate to those dates, then exactly how are you trying to determine the ranges to be summed?

rylo

[bobwrong]

Ok - here is the relationship with the dates (and you missed a set of dates - there are 3 different sets of dates on WK2 - each corresponding with a different range I need from place Sales Sheet)


WK2:

B8: =sum('place Sales Sheet'!F4:F10) If you look at place Sales Sheet F4:F10 you will see that the dates on the sheet match B10 - D10.

B20: =sum('place Sales Sheet'!F8:F14) If you look at place Sales Sheet F8:F14 you will see that the dates on the sheet match E2 - G2.

C20: =sum('place Sales Sheet'!B10:B16) If you look at place Sales Sheet B10:B16 you will see that the dates on the sheet match J2 - K2.

The formula you gave me originally did that just fine in the first dummy file but does not seem to work in this one. Thanks and I hope that answered your question.

[rylo]

Hi

OK, starting to make more sense.

Couple of problems.
1) Dates on place sales sheets are 2012 - not 2013 as E2, G2, B10 and D10
2) Data from rows 34 onwards in place sales sheet column F is all #REF!

That aside, try the following which will have to have their ranges extended when you fix the problems in the place sales sheet.

B8: =SUMPRODUCT(--('place Sales Sheet'!A3:A33>='WK 2'!B10),--('place Sales Sheet'!A3:A33<='WK 2'!D10),('place Sales Sheet'!F3:F33))
B20: =SUMPRODUCT(--('place Sales Sheet'!A3:A33>='WK 2'!E2),--('place Sales Sheet'!A3:A33<='WK 2'!G2),('place Sales Sheet'!F3:F33))
C20: =SUMPRODUCT(--('place Sales Sheet'!A3:A33>='WK 2'!J2),--('place Sales Sheet'!A3:A33<='WK 2'!K2),('place Sales Sheet'!B3:B33))

rylo

[bobwrong]

Sorry I didn't reply sooner - but that worked like a charm. Now I understand how that formula works and was able to adapt it to other applications as well. Thank you SO SO SO much for your help -

Hi

OK, starting to make more sense.

Couple of problems.
1) Dates on place sales sheets are 2012 - not 2013 as E2, G2, B10 and D10
2) Data from rows 34 onwards in place sales sheet column F is all #REF!

That aside, try the following which will have to have their ranges extended when you fix the problems in the place sales sheet.

B8: =SUMPRODUCT(--('place Sales Sheet'!A3:A33>='WK 2'!B10),--('place Sales Sheet'!A3:A33<='WK 2'!D10),('place Sales Sheet'!F3:F33))
B20: =SUMPRODUCT(--('place Sales Sheet'!A3:A33>='WK 2'!E2),--('place Sales Sheet'!A3:A33<='WK 2'!G2),('place Sales Sheet'!F3:F33))
C20: =SUMPRODUCT(--('place Sales Sheet'!A3:A33>='WK 2'!J2),--('place Sales Sheet'!A3:A33<='WK 2'!K2),('place Sales Sheet'!B3:B33))

rylo