Solved

Adding a year using exiting formatting in a worksheet

Posted on 2014-07-26
38
72 Views
Last Modified: 2014-07-28
Folks,
I am so grateful to all EE's that have stepped up helping with this project. I've been asked to "expand" it and some of the work has already been done.
Let me explain what happens first, then go into my objectives. There are two workbooks- "Consolidated" and "Append". When the user wished to track costs of a project they select the form command button labeled "Add Project" In doing this they will be asked to enter a four digit year. The four digit year populates B6:B17. Next the user needs to add new tabs to "Consolidated" for the year extrapolating month of the year from the four digit year first enter. If the enter 2014 that populates B6:B17 new tabs are added to the "Consolidate" worksheet. For example, Jan 14, Feb 14...Dec 14.
The last thing is that formulas for each month for each category are added to the "Consolidated" in the range C6:U17.
These formulas comes from the monthly total of each month for each category (B37:U37 would be for  Jan 14) and are posted to the "Consolidated" sheet for Jan 2014 (C6:U6)

When the user wishes to add another year to the project (which is what the macro "InsertNewYearConsolidated" is for and works some) three things need to happen.
1. If the first year was 2014 and they wanted to add 2015 they would need the first year to be a sub-total and subsequent years to be subtotal also. For example, 2014 would be subtotal, 2015 would be subtotaled. Let's say that this project only lasted two year. After the last year there would be a grand total for the project cost. Now if they have only one year no subtotaling would be required.
2. Notice that in B6:B17 is the four year. In this example use 2014 when you click on "Add Project". If a second year was to be tracked then there there's a need to have the new year would need read 2015.
3. Finally, in the first year formulas are placed in  C6:U17. In the next year formulas would be needed in the new year.

Here's my first objective. I'll follow-up with the other two objective is separate questions.
2. When the user select "Add Year" could we not simply add one to the year already in B6:B17. For example B6 = 2014 then the next year what ever the cell is would read 2015 and so for the for each of the 12 months.

Please keep in mind that the user may have a 1, 2, 3, 4, 5 year project. I'm trying to avoid a little user input as possible.

I've attached the files needed.
Consolidated.xlsm
Append.xlsm
0
Comment
Question by:Frank Freese
  • 20
  • 18
38 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40221965
Well you could just make this change

'    Yearly = InputBox("Please enter a 4 digit year to append to the added rows")
'    If Yearly < Year(Now) Or Yearly > Year(Now) + 5 Then
'        MsgBox "Invalid Year please try again"
'        Exit Sub
'    End If
    
    Yearly = Range("B6") + 1

Open in new window


but that would overwrite the year that is already there so I guess we need to do a subtotal and then add new rows.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40221968
I just downloaded the new workbook and noticed that you still have the red text in V2:X2 and it should be removed.

I think we need to ask the user if this is a new project or a subsequent year of a multi-year project. Unless we can use the "Project Closed" option button state. Comments?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40221972
You should also get rid of all the modUpdate<month>Formulas modules and the modUpdateFormulas module.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40221982
Please ignore the "I Think..." comment of mine in post ID 40221968. Apparently I wasn't thinking.
0
 

Author Comment

by:Frank Freese
ID: 40221989
Red text has now been removed from V2:X2 - missed that.
I think "subsequent year of a multi-year project" a single click, such as "Add Year" would be better.
A little history behind the Project Open / Project Closed buttons. I was thinking database where there was a query on Open / Closed workbooks and forgot this is Excel not Access. Then the user could print the totals on a separate report for all open projects. But I don't see a life in that because who knows where they're going to save their projects.
0
 

Author Comment

by:Frank Freese
ID: 40221992
"I think" has been ignored.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40222001
OK understood.

We don't seem to be currently using InsertNewYearConsolidated. If we do please remind me how.
0
 

Author Comment

by:Frank Freese
ID: 40222010
Does the workbook I sent you have a form command button on it label "Add Year"? I added that which calls the macro InsertNewYearConsolidtaed. If not, this attachment does. Click on it - it will ask you for the location for the "Append" file and add new tabs
0
 

Author Comment

by:Frank Freese
ID: 40222012
guess it would help if I included the file -
Consolidated.xlsm
0
 

Author Comment

by:Frank Freese
ID: 40222021
When you had run "Add Year" you be ask to put in a four digit year.
It should look like this. The tabs look ok. The year in B6:B17 was 2015 and has been replaced by 2015 because it is hard coded to populate that range.
newyear.jpg
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40222042
Let's nail down the requirements.

If someone clicks 'AddYear' I assume that...
13 new rows (including one for the previous year's subtotal) should be added
The "Yearly" value should be determined by adding one to the previous column B value
Totals should reflect all the years in the workbook.        

If someone clicks 'Add Project' I'm not so sure what should happen. Should the current workbook be saved as <ProjectName>.xlsm and then everything cleared out of the current wb and AddYear run?
0
 

Author Comment

by:Frank Freese
ID: 40222055
1. If "Add Year" is select then the project is more than 12 months or 13 new rows to include subtotal row. There would be a subtotal for the first year and one for each subsequent year. Each subsequent year after the first would have a Total Project Cost, which could be nothing more than adding the subtotals. Totals have to be in all workbooks, regardless if it is a one year project on crosses over into another year. For example, a three year life would look something like this: 2014 - subtotal, 2015 - subtotal, 2016- subtotal - total.
2. If some one click "Add Project" then a new project or workbook is being created. I thought about saving the new project named <ProjectName>.xlsm. However, I have no idea how they want to mange the naming of their files - they may want something like <ProjectName><Location>.xlsm. I'll let them make that decision. Also, the user may have named the project one thing and saved it as something else. In that case it is still their problem.
3 I'm not sure what you are asking then everything cleared out of the current wb and AddYear run
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40222115
#3. I meant that the current dat would be saved under some name and then the current WB would be refreshed to look like the Consilidated WB looks now, in other words it would have no data.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40222135
I'll work on this tomorrow.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40222705
Before I make code changes can I get your approval for these heading changes?
Heading changes
0
 

Author Comment

by:Frank Freese
ID: 40222720
On the above changes - are you simply wanting to move the Add Year to another location - if that's the case go ahead
#3: Now I have been told that they plan to resave these two files as template files. I never gave template files.
Although I've outlined the objectives, I have no problem breaking this into separate questions. There's a lot here.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40222733
On the above changes - are you simply wanting to move the Add Year to another location - if that's the case go ahead
There are several changes shown:
1.    'Add Project' is now 'New Project'
2.    'Project Name' is moved from row 1 to 2
3.    There's no more "Project Name" text
4.    The green heading is one column wider

Is it OK?
0
 

Author Comment

by:Frank Freese
ID: 40222740
No problem. I like these changes
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40223142
Here is a preliminary version. Currently only 'Add Year' (which is the adding of a year to an existing project) works. Also there's a bug that causes an extra subtotal line to be created for the 3rd year. I'm going to take a break and get back to this later at which time I'll fix that bug. Let me know if you find anything else that's wrong.

I'm going to be away Tuesday to Saturday.
Consolidated-V2.xlsm
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Frank Freese
ID: 40223248
I've looked at it. Since I couldn't start from the beginning with "Add Project" - command button unassigned, it's hard to respond but it looks like you're heading in the right direction. If I saw something I'd sure tell you
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40223258
For now just click the 'Add Year' button.
0
 

Author Comment

by:Frank Freese
ID: 40223265
OK - I see the double subtotals you're referring to and I know you'll be correcting the grand Total. Martin, this is pretty awesome. I couldn't find anything else.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40223272
Look at the red border between columns G and H. Is it correct for all the cells?
0
 

Author Comment

by:Frank Freese
ID: 40223277
If you can carry that formatting all the way down (to include subtotals and total) it'll avoid from them a "How come you didn't extend the red line between G and H?" If not, you know they're going to just live with it.
0
 

Author Comment

by:Frank Freese
ID: 40224212
#3. I meant that the current data would be saved under some name and then the current WB would be refreshed to look like the Consolidated WB looks now, in other words it would have no data.

You made this comment earlier and I kind-of brushed it off. Your question and my response has haunted me until last night  - I think I'm plugged in now. For example, once a user has set up a project there is no option except to exit to save the project. Therefore, I messed up here and you above suggestion is TRUE.

1. If the user wanted to add a New Project then they could have the option of saving their work and reloading "Consolidated"?

Separate question for this please but was is this what you were speaking to?
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40224244
Yes that's what I was planning except that it wouldn't be an option. In other words if they clicked the 'New Project' button they would see a message like "You are about to create a new project. Your current project will be closed and then saved as <the project name>. Click 'OK' if you are sure."

Here's the workbook with 'Add Year' working properly. I also added two small improvements. The code handles the pressing of the 'Cancel' key when appending a new file, and the file selected file is validated to make sure that it's a cost allocation file. All changes are marked with "Subtotals".
Consolidated-V2.xlsm
0
 

Author Comment

by:Frank Freese
ID: 40224257
Since the workbook opens unpopulated, once data has been entered and they wanted to add another project we would do this:
Workbooks("Consolidated.XLSM").Close SaveChanges:=True

Open in new window

that just leaves reopening "Consolidated"? Now I've got myself lost regarding the Workbooks.Open method right after they Save. Would it be as simple as
Workbooks.Open "Consoldiated.xlsm"

Open in new window

I can't speak to templates however.
0
 

Author Comment

by:Frank Freese
ID: 40224281
We just missed one another's post - I like your proposal the best on  your last post ID: 40224244. We can make this a separate question if you like.
The w/b you just sent looks great (I know you're working of the grand total). Pretty "nifty" programming. Almost there!
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40224310
I don't understand the need to reopen "Consolidated". Here's the way I see the project being used.

Initially they start with a workbook that looks like this.
Initially1.    They add data for the first year and then perhaps go away for a while. At this point the workbook doesn't need a project name. BTW I'm planning on adding a small bit of code which for a new project the 'Year' values would be the current year rather than always "2014".
2.    At any time they can add a new year.
3.    At any time they can start a new project by clicking 'New Project' (or selecting the 'Project Closed' option?). If they click 'New Project' and they haven't added a Project Name, they will be asked to do so. If they click 'New Project'  and there is a project name the workbook would be saved as "Consolidated <project name>.xlsm" and the workbook would be initialized so that it looks like the picture.

Please carefully consider the above and let me know if you agree or not and/or if you have any questions.
0
 

Author Comment

by:Frank Freese
ID: 40224329
You said it better than I did and I'm on the same page with you now. I agree with the changes.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40224378
What about the part where I mentioned "or selecting the 'Project Closed' option"? When that option is selected what if anything should happen?
0
 

Author Comment

by:Frank Freese
ID: 40224414
Good point - projects will open and close at various times or at the same time. The only think I can think of is if they select Project Close would be to allow them the option to start a new Project, else Close the workbook. If they don't have another project and they Close that project the only thing I can think of is to close the workbook.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40224441
OK I'll keep that in mind for later. If you post a new question for the 'New Project' functionality I'll start but since I'm going away for a few days I won't be able to finish it. Perhaps someone else will.
0
 

Author Comment

by:Frank Freese
ID: 40224461
I'd prefer to wait till you get back. But when you do I'll open a new question then.
Have a safe trip
0
 

Author Comment

by:Frank Freese
ID: 40224472
I'm consider closing this and accepting ID: 40224244 as a solution. That will keep the question from appearing unattended. OK with that?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40224492
Sure!
0
 

Author Comment

by:Frank Freese
ID: 40224503
You can pick up with the new question that I'll post upon your safe return.
0
 

Author Closing Comment

by:Frank Freese
ID: 40224507
Great job - thank you kindly.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now