Excel - Make Master workbook from data in many other workbooks 1

[Jeanie]

I have no idea how to do what I need. Excel is probably not the best place to do it, however, everyone in my office has excel so that is where I was hoping to get it accomplished if possible. Any help anyone can give me would be greatly appreciated!

I have workbooks where I am tracking some specific information for different processes in our department. The information is all text. I want people to be able to enter the information in the individual workbook that they are responsible for, but, I want it to all roll up into a master workbook where I can see the information for all the workbooks in one place. The columns will be labeled the same on each workbook, and be in the same place. The rows, however, will not necessarily be the same in each workbook. But, if a row is present on a workbook, it will have the same information in the 1st 2 columns (ID and Label). At the end I need an identifier for which table it comes from. Eventually, I will have close to 200 seperate workbooks that need to roll up into this master workbook.

Below is a simplified example:

WORKBOOK A
ID Description Resources Weakness

AC1 Access Control Staff Training
BB5 Inventory Prep Space Infrastructure
ZD7 Systems Audit Technology Coordination

WORKBOOK B
ID Description Resources Weakness

AC1 Access Control Staff Hiring
BB2 Maintenance Staff Equipment
ZD7 Systems Audit Technology Coordination

CONSOLIDATED WORKBOOK
ID Description Resources Weakness Table

AC1 Access Control Staff Training A
AC1 Access Control Staff Hiring B
BB2 Maintenance Staff Equipment B
BB5 Inventory Prep Space Infrastructure A
ZD7 Systems Audit Technology Coordination A
ZD7 Systems Audit Technology Coordination B

Thanks In advance for all your help!

Jeanie

 

[SkipVought]

Hi,

The columns will be labeled the same on each workbook, and be in the same place.

Tilt!!! AC1 is DIFFERENT!!!

I thought, as I began reading, that I knew what you wanted, but your examples are complete baffling!

Table examples would be good. Sample uploaded workbooks would be even better: workbooks A, B & Consolidated.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[zelgar]

If you're planning to go the Excel route (a database might be better, but limited to 1 user at a time). I'm thinking you'll need to have a macro to gather the information from the 200 different sheets and then consolidate the information into the master sheet.

 

[Jeanie]

Sorry about being so unclear!

I do not know how to upload more than one workbook to this thread so I have combined the different workbooks into different sheets. But, in "real life" they will each be a stand alone workbook.

To clarify, every time AC1 is listed it will have the exact same description and location. However, the status will most likely be different. So, an ID is statically tied to a Description and Location, however, the Status can be anything.

Hope that helps! And, thanks in advance for any help

Jeanie

 

[SkipVought]

Ah, your IDs look like cell references: hence my confusion.

I'll look at this later tonight.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

It is certain that you will need a macro to accomplish your task.

You will need to make a list of all the workbook names. Will all these workbooks be stored in one folder on a network server? What is the Server name and path to the folder(s)?

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Jeanie]

I do not currently have all the workbook names. And yes, they will all be in one folder on a shared directory. That directory is (I PMB(\\hhs-pmbshare\Shared\DIR\Systems

A few of the workbook names:
AWACS
Sweetgrass
CHIMES
KIDS
SEARCHS

Thanks,

Jeanie

 

[SkipVought]

Download this workbook and test it with your current data. Let me know how it works.

It should open each workbook in your folder (the PATH is in the Factors sheet, which you can change at any time if need be), COPY the data, PASTE it into the tConsol table and label the workbook name.

It should be a starting point for you.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Jeanie]

Hmmmm...Nothing fills in? I did change the path because I'm working at a different location today so don't have access to that path. Maybe I entered it incorrectly? the path reads as: (GCS8909(\\hhs-hlncg\Users)>Jackie. I entered it as: \\hhs-hlncg\Users\Jackie. I put all the files into the folder "Jackie" and they are the only documents in that folder. When I open windows explorer to open the file I do see Allworkbooks flash at the bottom like it is running thru the macro, but nothing fills in. Do the names of the workbooks matter?

Thanks for all your help!

 

[SkipVought]

I didn't have a chance to test the code until this afternoon. It runs sucessfully.

I made a tweek. Please use the attached workbook.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[Jeanie]

THank you for your help! I think this will work great!

Jeanie

 

[SkipVought]

Great. If you need help modifying the code or adding additional features, please post a new thread in forum707, in which you can get other expert VBA coding help.

Hope your project is a success.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}