PDA

View Full Version : Consolidating multiple sheets onto one "parent" report sheet.



ktacserv
01-30-2011, 07:22 AM
Help - I have been working on this for two days and cannot figure this out!!

We use excel at work to track each project manager's proposals. Right now, this tracking is being done seperately by each department, but the company would like to begin asking each PM to use the same excel template for consistency. The idea is that each PM will have a sheet to fill out in this master excel database, and after they fill out their own sheet, the "Parent" sheet will be automatically populated with all of the data - creating a report that will show all of the proposals company-wide. Make sense?

I would love to upload this spreadsheet somewhere to better show what I am trying to achieve - is that possible?

I have done a lot of research on how to best consolidate my worksheets into a single parent sheet and have played around with the "Consolidate" function - which did not work for my purposes. I came to the conclusion that I most likely need to write a macro to accomplish this. I am new to macros and have been trying to search the internet far and wide to figure this out, and have written a few (busted) macros that never work the way I need them to.

Here are some important factors in this particular excel database:

1. There are no numbers involved on these sheets - it is all text fields. I simply need the text from each sheet translated onto the parent sheet. I do not need summation or any other math formulas right now.

2. Inevitably, as our PM's fill out these sheets, there will be some fields left blank. I need this to be allowable, and I need the parent sheet (the report) to be able to translate these blank spaces EXACTLY as they appear on the templates that are filled out by each project manager. If a PM leaves a field blank - it is left blank for a reason - and it needs to appear as a blank in exactly the same place on the report.

3. We do not have use of MS Access in our office - this has to be done in Excel.

4. One last thing - it would be preferable to have the report sheet continually updated as is - WITHOUT recreating the entire sheet each time that I need to update it. Many of the macros that I have found online create an entirely new sheet each time that you need to update it. The reason for keeping the parent sheet the same without completely re-creating it each time, is that the president likes to highlight fields, change text color, etc. for his own reference purposes. If we re-create the sheet each time, it will erase his highlighting system.

Sorry if this is not enough information, if you have questions please ask. I would really appreciate any and all help - I am desperate over here!

P.S. - I am very new to VBA/macros so please go easy on me!!

GTO
01-31-2011, 12:57 AM
Hello ktacserv,

Welcome to vbaexpress:hi:

To attach a workbook, press the 'Go Advanced' button below the 'Quick Reply' box. In the new window, below the 'Reply to Thread' section, you'll see 'Additional Options' and a Manage Attachments button therein.

I am sure that you'll have fun here, its a great site!

Mark