Excel Calculation across 2 sheets, and freesing at end of month

Please hang in there while I ATTEMPT to explain what I'm trying to achieve. I'll do this as simple as possible. On sheet one column C there is a check mark placed upon completion of a project. C46 calculates the percentage of work done depending upon the amount of check marks placed in C1-C45, this works fine. Sheet two row 3 are the months of the year, B3=Sept, C3=Oct, D3=Nov etc etc. I need sheet 1 C46 to populate in sheet 2 B4 until the end of the month and then freeze, At this point we're in the next month ( C5 ) and C46 should now start populating C5, and so on through the months of the year. The goal here is to  check off finished work on sheet 1 as it is completed with the percentage ( C46 ) populating in the current month on sheet 2, at the end of the month sheet two freezes the percentage for that month, and C46 then begins calculating the remaining check marks if any in the following month on sheet 2.


 I am a novice at best with Excel so please excuse me if I'm unclear or missing needed information, please ask questions, I'll be happy to try and clarify, this is an extremely important project to me. Thanks again.

Oh and BTW Excel 2010 from Office ProPlus 2010
LVL 1
McottuliAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PhonebuffCommented:
If you are reusing the Columns C1:C45 To get the value in C46 each month then you can't do this automatically.  

However, at the end of the month you could copy the Formula in Sheet 2 B3 to Sheet Two C3  so that it started over.  =Sheet1!$C$46 .  An before you clear or change anything in Sheet 1 Column C1:C45 enter or Paste Special the Value from C3 into the cell B3.

===================================================
0
McottuliAuthor Commented:
Thank you for the response, unfortunately this supposed to be a corporate template and I can't ask the people I'm supposed to provide it to, to make edits each month. Would there be a way to embed a script that would achieve your manual suggestion above?
0
PhonebuffCommented:
I supposed you could build a macro that could recognize that it's the first time a workbook has been opened in a given month and to execute before allowing any changes --

"Excel, has the ability to automatically execute or run an Excel macro code. Macros are the set of commands used to create, record and save repetitive tasks. If you want to autorun a macro when opening a worksheet then you need to type your macro name in the Worksheet_Activate() event and this code will run a macro when opening your Excel spreadsheet. "
0
Increase Security & Decrease Risk with NSPM Tools

Analyst firm, Enterprise Management Associates (EMA) reveals significant benefits to enterprises when using Network Security Policy Management (NSPM) solutions, while organizations without, experienced issues including non standard security policies and failed cloud migrations

McottuliAuthor Commented:
That is what I thought. The question is, What would the needed script be?
0
PhonebuffCommented:
The Script needs to do what I suggested in the first post --

But I would suggest you find a local consultant to write it for if you are not comfortable doing it yourself..
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
McottuliAuthor Commented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Hardware Firewalls

From novice to tech pro — start learning today.