Working with an option button

Frank, my understanding was that for now the option button wasn't going to have any actual functionality for now and that you wanted instead functionality behind the 'New Project' button. Please verify.

I actually don't see a need for the option buttons. If the name of the 'New Project' button was changed to 'Close Project' and if after the user clicked that button the project was saved and then closed, would that do?

If you want that then I can have it for you in an hour or so,

The user "likes" the two option buttons. It's informational for them and I'm ok with that. I think leaving it as is would be the best course of action.
The "New Project" button would ask the user if they wanted to save the workbook they are in. Regardless of the answer,  the workbook they were in is closed. The user would then have to go to disk and locate the "Consolidated" file to load. If they elect to cancel, Exit Excel. It would function the same as when we append tabs to the Consolidated for Add Years.

Give this a try. Note that the Grand Totals are incorrect but I'll fix that. Do you want it to say 'New Project' or 'Close Project'?
Consolidated-V3.xlsm

Several things
1) When I click on "New Project" it is asking for a Folder Name? I couldn't go any further.
2) We need to check to make sure that the user has assigned a Project a name before saving a Project or adding a New Project. No name - no save.
3. I noticed that when I had three years the third year formulas for January was 0 and February was January, etc.
4. Let's stay with "New Project"

1) Why not? It defaults to the place where you have the Excel file, so just click OK.
2) Already being done.
3) May be dues to the formulas not including January which I've corrected, but I'll check tomorrow.

Wow! I'm going to have to chew on what you did - haven't seen it done that way before - ever.
Will wait for the corrected version tomorrow.
Welcome back!

Do you have an Append file that contains non-zero data?

See attached
Append.xlsm

I think you attached the wrong wb since there does not appear to be any non-zero data.

You did mean the append?
The attached file has zero values but only for the total for each month.
You're not thinking the Consolidated by any chance?
Append.xlsm

Yes I do mean an Append file and what I was asking for was one that has non-zero data, so for eaxmple one that looks like the following so I could test the sub- and grand-total formulas.
As you can see I have one now so no need to attach one again.

I've attached a new Consolidated wb that I believe fixes all known problems.
Consolidated-V3.xlsm

I look at it now - had some networking issues to resolve

The only problem I've seen is that when a w/b has been save then reopened to add another year when one goes to "New Project" the following occurs of the open project file.
I haven't tested adding data to years and checking subtotals / grand totals once I ran into the above problem.
What I've seen is awesome - I'd never thought that this could be done. So far a fantastic job!

I can't reproduce the problem, probably because I didn't do exactly what you did. The following are the steps I took that did not result in an error. Please list your steps in the same fashion.
1.    Open Consolidated V3
2.    Click 'Add Year'
3.    Click 'New Project'
4.    Click 'Add Year'
5.    Click 'New Project'

Interesting - I can't recreate the problem either.
But I did notice something. The user opens Consolidate V3 and enters some Jan 14 data. When they go to create a "New Project" the current project is saved and a new project file is opened with the data from the existing one still? Also, enter some data in any month and then Exit the program. The file name to save is the same as the original. On New Project you have the file named "Consolidated xxxx", which is different then what happens when simply entering data and Exit the workbook. At least that's what I'm getting.

Would you mind as I go through this identify each problem as I find them?
For example, when I opened Consolidated-V-3.xlsm and enter data for Jan 14 Billable hours the formula is missing. Or do you want them all at once?

I'd prefer more than one at a time unless there's a major problem.

Anyhow, while I was making the recent changes I was thinking about the fact that if you manually enter something in the Consolidated sheet that that would not be reflected in the details sheet and I was going to ask you if we should protect the Consolidated sheet. The problem you just reported makes that pretty much a requirement because if you enter something in a Consolidated cell that has a formula, that formula is replaced by the value, so should be protect that sheet?

I'm ahead of you on this. Here's what I have seen so far:
1.      For January 14 no billable hours formula – just 0. I believe you've identified the why - good point!
2.      When clicking on “Exit” and there is no Project Name the Excels “Save, Don’t Save, Cancel” dialog box appears. If there is a Project name the same dialog box appears. The problem here is that “Consolidated-V3” is overwritten regardless. Protecting a workbook with a password really doesn't solve the problem since it is so easy to find the password.
3.      I truly, honestly, would like the "Consolidated" and "Append" workbooks to be template files and let them place those files in a folder where all the Project w/b's are kept. As you mentioned there needs protection on these two files. This may sound weird but after 2003 I haven't figured out where 2010 and above template files are kept. I've never had the need to know until now. I'll do some research on that.

Finally found where the template files are located. If you didn't know you have to open Word - File - Option - Advanced then scroll to the bottom to see file locations. I would have though Excel would tell you the same thing. Weird

2. If you first rename the file as "Consolidated" it will work differently, and that's the way you should test it because it's what your users will experience.

3. I have no experience with templates so if you really want to use them then someone else will need to do it. Why do you need templates anyhow?

3. Templates is not the final solution - the only part of the worksheet not protected would be the where the Project Name goes. All formulas have to be protected. I could handle templates now that I know where they're stored.
2. Understood and I'll test it that way and with using a template -  but will do it tomorrow.
I'll be so glad when this is project is done because I need some help on the Statistics workbook.
I'll need to work through this first part - thanks.
Appreciate you!

We can do both, no?

Yes we can. I've already created the templates for "Consolidated" and "Append"
Question here?
You've gone far and above the initial objective and I think we've beat this dog to death. I would like to test and submit a questions for each new problem. I suspect we're "very" close to bringing this home. Please advise.

Sure.

Thanks...new question being developed

thanks - great job

I feel a little like a drug dealer enticing you to take some more, but in the attached workbook I've locked the data cells (sheet password = ml), but click on any data cell (other than the month and year) and see what happens.

BTW there's a bug. If you manually type in the Project Name it's not propagated to the other sheets.
Consolidated.xlsm

I knew of the "bug" and mentioned it to them that they only way to populate the project name across workbooks is using the Project Name button.

In regards to protection - I took care of that before I sent them the final file. Not only did I have to protect all formulas but unprotect the location of the project name on all Consolidated sheets (also corrected the Append w/b also). They didn't like they fact that they can't make their own changes - whatever that means. So, to make them happy I told them to make a copy of either file saving it to the same location. Make whatever changes they make to the copy version they can over write the original file. It is understood that no additional support will be provided. What they did like was that it is only about 3 - 4 steps and that was nice.

I bet you feel like you've been on a roller coaster. Maybe you can get back to some simpler questions? Almost, for I'll be posting one on the Statistics w/b - but I tried to do it myself first. Just to let you know what type of guy I am I'll wait a couple of days (I need it myself).

OK but make sure that the workbook that you passed over to them doesn't have "xxx" as the project name on the detail sheets.

whoops - but that'll be overwritten when they put in a project name.