Internship Help: Linking worksheets to other worksheets automatically

[cuervor14]

Hello,

I am a young college student that is interning for a business this summer. I am excel certified but I am in no way a "master" of excel. Right now they want me to make two worksheets, one is a budget sheet and the other is the overall month budget sheet. On one sheet, they want to be able to right all the expenditures of an event down. On the second sheet, they want to have all the statistics of all the expenditures that happened in the entire month.

The thing that makes this complicated is that they want it so that whenever you type in a month on the budget sheet, that information will automatically go to the overall month budget sheet. Thus in a way they want it to be "connected".

Is there any way to do this? I thought that a possible way to do this is that they can organize a list of what budget sheets (Lets say, budget sheet A01-A05) they want, and then import it. But this doesn't seem possible anyways.

Thank you for any and all help. Please let me know if there is any more information

 

[mumps]

It is difficult to help without knowing what data the sheets will contain and how that data is organized. I would suggest that you design the budget sheets and the overall budget sheet with column headers to describe the data in each column. Save the file and then upload it to a free site such as www.box.com. After you upload it, mark it for 'sharing' and you'll be given a link to the file that you can post here. Include a detailed description of what you want to do referring to specific cells and sheets.

 

[cuervor14]

Hello,

Thank you very much for your help.

The information is put as you said it. There are columns headers with columns following it with the respective date. For example, it will have "Marketing Expense" and then have the dollar value right after it.

Having the overall budget sheet pull the information from the individual budget sheets won't be a hard thing to do, the problem lies with how to connect or import them. Thank you for the idea of importing it from an online source, that is something I had in mind. Thank you!

 

[mumps]

I'm not suggesting that you import from an online source. I was suggesting that it would be easier to help you if I could see a copy of your file. Follow the instructions I described in Post #2 to upload your file:

Save the file and then upload it to a free site such as www.box.com. After you upload it, mark it for 'sharing' and you'll be given a link to the file that you can post here. Include a detailed description of what you want to do referring to specific cells and sheets.

If you are able to do this, I will be able to download your file and have a look at it.

 

[cuervor14]

Here is the link: https://app.box.com/s/fvphmkpgl69fadt5i0laqm51hck7tlly


So let me explain things again so that they are clear.
"Initiative Task Sheet" is meant to be just a short cut to the individual worksheets. Just for convenience so that you don't have to be scrolling so far.

"Budget Sheet" is the template that the business will be using when describing what events/socials/marketing they do and how much they spend on it. What is important is A22-D29. This is what needs to go into the overall Budget sheet.

"March Sheet" is an example sheet of what the overall budget sheets will look like. There will be one for every month. Let me explain to you again what they want. they want it so that whenever there is a new Budget Sheet, for example "C40", the overall budget sheet(s) will look at that worksheet and think "Okay this event was done in march". So the information for that sheet will go into the "march" overall budget sheet.

Again, my problem is not figuring out how to make the overall budget sheet look things up for ONE specific budget sheet.

 

[mumps]

A few questions: You currently have one overall budget sheet. Can I assume that there should be 12 overall budget sheets from January to December? How and when are new budget sheets created? Each of the budgetary expenditures in the overall budget sheet has room for a different number of events. For example, Labor has room for 7 events while Marketing has room for only 2 events. Is this how it should be? I would think that each expenditure should have the same amount of room (rows) to hold the maximum number of events. Could I also suggest that it might be better to have the expenditures beside each other rather than under each other. In all of your sheets you have several merged cells. They should be avoided if at all possible because they present problems when programming in Excel. The manner in which data is organized will affect how easily and effectively a macro can be designed. I realize that I have asked a lot of questions but this information will help in writing a macro that will work.

 

[cuervor14]

Here are the answer to your questiions:

Yes there are supposed to be 12 overall budget sheets. I just only made the March sheet as an example.

There are supposed to be multiple number of events. What they want to happen is that the Overall budget sheets adds rows as new budget sheets are created. So lets say I have 8 budget sheets for march, that means that there will be 8 events for every category in the overall budget sheet for march. But then if i go and create another budget sheet that is in march, the overall budget sheet is supposed to create another row and add all of that information.

Could I have you explain this "Could I also suggest that it might be better to have the expenditures beside each other rather than under each other."

I didn't realize that merging cells created problems for programming. I will try my best to fix that problem.

 

[mumps]

In the overall budget sheet, will the date be the same for each budgetary expenditure for a particular event? For example, if the event A01 occurred on June 1, 2015, will all the budgetary expenditures for that event have June 1, 2015? If it's OK with you, I would like to re-design the overall budget sheet to make programming easier.

 

[cuervor14]

Yes it will! And please go right ahead. Whatever you do will probably be better than what I have.

 

[mumps]

Please click here to download your file. Make sure that the newly created budget sheet is always the active sheet before you run the macro to update the overall budget sheet. Select the sheet "A01" to make it the active sheet. We'll assume that this is the newly created budget sheet. Hold down the CTRL key and press the letter "u" to update the overall budget sheet. Now have a look at the overall budget sheet. You'll see that the data for the event has been copied over and that the total monthly expenditures have been calculated. I wasn't sure how you wanted to calculate the % for Labor, Cost of Production, Event Cost, Marketing Cost and Miscellaneous Cost. If you describe how you want these percentages calculated, I can modify the macro to enter those values.