Weekly and Monthly Goals - Help

[dinokath]

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!


https://drive.google.com/file/d/0B3p-uEP_miD6b1h6RmhPbk9GSmc/edit?usp=sharing

 

[RonB1111]

Your excel file on google drive is blank.

 

[dinokath]

https://drive.google.com/file/d/0B3p-uEP_miD6LXRva29hdDdOanc/edit?usp=sharing

So sorry! Thank you!

 

[bschwartz]

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.

 

[RonB1111]

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:

=SUMPRODUCT(--(MONTH($B$3:$MO$3)=MONTH(B$17)),$B4:$MO4)

 

[dinokath]

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?

Here's the updated file: https://drive.google.com/file/d/0B3p-uEP_miD6enUtYnV1YW9lNHc/edit?usp=sharing


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

 

[RonB1111]

You're welcome!
Here's the modified formula to enter in B18, then copy down and across:

=SUMPRODUCT(--(MONTH($B$3:$MO$3)=MONTH(B$17)),$B4:$MO4,--(YEAR($B$3:$MO$3)>2000))

 

[dinokath]

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!

 

[RonB1111]

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