Weekly and Monthly Goals - Help

dinokath

New Member
Hi all,

Thanks in advance for the help on this. I'm going nuts trying to figure this out.

My goals change frequently and I need to be able to update the spreadsheet in order to reflect that.

What I need to do is take the values from the Goals sheet and have them autopopulate in the Weekly-Monthly sheet and have them change by day and by organizational center. I also need the sheet to add up the goals based on the daily goals by month. I am crosseyed trying to figure this out!

Thank you all so much for any help or advice!

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Can you fill in a few expected values and re-post the spreadsheet? Say, for Albany for the month of September, just manually populate the expected results.

Enter these formulas:
In B4: =Goals!\$C2
In C4: =Goals!\$D2
In D4: =Goals!\$E2
In E4: =Goals!\$F2
In F4: =Goals!\$G2
In G4: =Goals!\$H2
In H4: =Goals!\$B2

Now select B4:H4 and copy down.
Now select B4:H14 and copy the block to J4, R4, Z4, etc.

On Row 17, change the dates from Sep to 9/1/2014, Oct to 10/1/2014, etc. You can then do a Format Cells - Number Tab - Custom and enter ddd in the box under Type

After changing row 17, enter this formula in B18 and copy down and across:
Code:
``=SUMPRODUCT(--(MONTH(\$B\$3:\$MO\$3)=MONTH(B\$17)),\$B4:\$MO4)``

Ron, you are THE MAN. Thank you!!

Tiny glitch though - when I copy the sumproduct code, all the months add up correctly except January. For example, it shows the goal for Albany at 1794, Athens 2230, etc. I cannot figure where I am doing it wrong?

Wow. I need to take a real class in Excel. Thank you all again!!

You're welcome!
Here's the modified formula to enter in B18, then copy down and across:
Code:
``=SUMPRODUCT(--(MONTH(\$B\$3:\$MO\$3)=MONTH(B\$17)),\$B4:\$MO4,--(YEAR(\$B\$3:\$MO\$3)>2000))``

Genius, pure genius. Thank you Rob. Thank you! I REALLY appreciate the help. I cannot even say it was help. You did it for me. Thank you, thank you!

You're welcome! - Glad it's working for you.

Replies
10
Views
429
Replies
4
Views
538
Replies
0
Views
85
Replies
8
Views
419
Replies
0
Views
308

1,221,525
Messages
6,160,328
Members
451,637
Latest member
hvp2262

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back