Consolidate data from multiple workbooks

[abdulji]

I have a master checklist workbook which should consolidate data from different workbook.

1. In the master checklist v1.xls, from C2:C6 it should populate the data from Checklist v1.xls, from C2:C6. Similarly for D2:D6 it should populate the data from Checklist v2.xls from C2:C6.

2. The above scenario should apply for the below Activities/Tasks table also

3. We will be having multiple sheets to capture the data for different SR Number. Is there a way to automatically add columns based on the number of sheets created and capture the data into the master sheet. Something like having the same naming convention (Checklist vXX.xls) and placing it in the same location..

For the above point 1 and 2, i tried to give a reference in C2=='[Checklist v1.xls]Checklist'!$C$2:$D$2 and the value gets populated but when I do the same in D2=='[Checklist v2.xls]Checklist'!$C$2:$D$2, I am getting "0" only. I also tried the consolidate option but still it works only for Column C and I am getting "0" for Column D

I have attached the sheets for your reference

Master Checklist v1.xls
Checklist v1.xls
Checklist v2.xls

[Richard Buttrey]

Can I ask the obvious question. Do you need to distribute your data across many workbooks?
Is it not possible to keep all data in a single workbook on a single database sheet but with an additional column to record the SR number for each unique record?

Perhaps if you describe your business process and how you actually capture data (e.g. manually input, feed from back office system), who enters it (one person or many) and if many at the same time or not.

[abdulji]

We have a team of 50 resources and we will be getting SR's all the time. Having one master sheet shared and accessed by all those resources at the same time will create conflicts thereby losing the data. So we thought of having one sheet for each SR to capture the details of that particular SR and consolidate it into a master sheet.

We can think of having 10 checklist workbooks in which the team can update it but I need a master sheet to consolidate the data from those 10 workbooks.

If you think of any other scenario, please suggest.

[Richard Buttrey]

Hi,

I don't understand your terminology What do YOU mean by 50 resources, and 'getting SR's all the time'. Do you mean that you have many people all needing to contribute information AT the SAME Time?

When you say one sheet for each SR do you really mean one WORKBOOK for each SR? Many people confuse the terms worksheet and workbook.

It's important we understand your business system in order to comment with any certainty so it would be helpful if you can describe your business process in simple terms. Rememebr we're not familiar with your particular terminology if it's unique to you and your business.

[abdulji]

Sorry for the confusion.

To keep it simple, we have 50 resources and each resource will have 1 checklist workbook. I need to consolidate all data from 50 workbook into one master workbook.

[Richard Buttrey]

Hi,

You haven't explained your overall business process as I requested.

Don't assume that what you are currently doing is the only or even best way of achieving your goal, it most likely isn't otherwise arguably you wouldn't be seeking assistance here.

I don't want to know what you currently do, I'd like to start from scratch knowing what data you start with (and how you collect it) and your end goal. Then I can decide how that may be best achieved.

When describing your business process and who / how many people are involved and when, don't use terminology that is open to interpretation. I have no idea what YOU mean by a 'resource'. I have my own definitions of things that could be resources but unless you tell me what you mean I just don't know.

[newdoverman]

Without the answers to the questions that Richard Buttrey asked, there is virtually no chance of providing a solution. All that I can offer is an example using 1 Master and 2 sub-mater workbooks to show what you could be up against.

[abdulji]

we have 50 people and they will be inputting manually the numbers of tasks completed in the checklist sheet against each task. We will be having 50 workbooks and we need a master workbook to consolidate the data from all the 50 workbooks.

[newdoverman]

If you are on a network, a better way of doing this is with a database that will accept input from multiple sources. You can see from the example that I gave to you, that system will get very large very quickly. Personally, for a project like this, I think that Excel isn't the proper tool for this.

[Richard Buttrey]

we have 50 people and they will be inputting manually the numbers of tasks completed in the checklist sheet against each task. We will be having 50 workbooks and we need a master workbook to consolidate the data from all the 50 workbooks.

With respect you're still just telling us how you currently see your solution. There may be other ways you haven't considered. I don't normally recommend using Excel's 'Shared Workbook' functionality since it has limitations, some serious for certain tasks, but it might be that this would allow all 50 people to contribute to a single workbook without you needing to consolidate individual workbooks.