Master Sheet with summary of info from tables in other sheets

I have a lot of spreadsheets (50+), each with various product codes, unit cost, quantity, total cost and date on which these products are to be ordered. Each spreadsheet will have a project name associated.


I need a master sheet with a summary of all these info. I want to know how much will be the cost incurred on a particular date/month for purchases with a break up. Hoping to get help soon.

Many Thanks,
Sujin

Copy range from multiple sheets into master sheet - with VBA

sujinv said:

I need a master sheet with a summary of all these info.

Click to expand...

I am a long time ago should have something similar. Here's an example, (if it helps you).
Attached file contain VBA code

Get all data from multiple sheets to one sheet

This example is perhaps better, see attached file

Details of requirement

navic said:

This example is perhaps better, see attached file

Click to expand...

Hi Navic,

I'm so happy to get a response so fast. Thanks a lot.

But I think I did not explain my requirement properly. I want my master sheet to tell me, on 'n'th date my cash outflow should be X amount. Also, all these sheets will be in different work books in different folders. I want the master sheet to update the content automatically if I make a change in the individual sheets. For example, if I change quantity of a product or date of order in individual sheet the change should happen in master sheet as well.

Can this be done?

-Sujin

sujinv said:

Also, all these sheets will be in different work books in different folders. I want the master sheet to update the content automatically if I make a change in the individual sheets.

Click to expand...

It is already possible database.
I think you need to give much more information for a final solution. Also, I think you need someone to do it programmed in VBA?
But it's a sizable job (my opinion)
I doubt that this can be solved using only formula. (Multiple folders, multiple workbooks, multiple sheets)?

Maybe you should seek help and solve using Access.
regards and good luck

I have arrived at two pivot tables for two sample projects. Can I merge data in these two tables to a new table which will be autoupdated? Direct linking and summing up will be a tedious task as I have lot of tables like this. Also, I need to get rid of "<04-01-2017" kinda things from my table.

sujinv said:

I have arrived at two pivot tables for two sample projects. Can I merge data in these two tables to a new table…

Click to expand...

Yes. But it will need macros (vba)

sujinv said:

…which will be autoupdated?

Click to expand...

Yes, asl long as you can easily point to the files concerned (are they named in a particular way, or are they all in the one folder)?

sujinv said:

Direct linking and summing up will be a tedious task as I have lot of tables like this.

Click to expand...

Yes, it will indeed, using pivot tables is the way to go.

sujinv said:

Also, I need to get rid of "<04-01-2017" kinda things from my table.

Click to expand...

We can but try.




What you need to provide is samples of the data files, say 3, and one extra file where you have used a pivot table to summarise data so that we know the kind of thing you want to see.

Sample Sheets

Hi p45cal, thanks for your reply.

I have attached three sample data sheets and a master sheet where I need all consolidated information. The format of master sheet can be anything, but i need data with the same detail. I'm sure we can do it using macros and pivots even though I'm yet to get the break through.

All these sheets will be placed in different adjacent folders for each project. And the master sheet will be in same disk but different location.

This is a start.
Put the attached in any location you want.
Put your 3 Sample files from your last message each in its own folder by itself (for the time being - the code needs refining) but those folders all themselves in the same higher folder (this higher folder is the one you'll select when you click the button on sheet9 of the attached).
Again, for the time being, don't alter anything on the sheets in the attached, but examine the pivot table and formula solutions before clicking the button (which if things are not correctly set up will screw up the sheets).
It works by querying the Sample files (not opening them). It expects to see a table called projectdata in each file (the same as you have supplied them).
I've used the file name as the project name in the absence of any other project name.

Thank you p45cal.

Delete as appropriate:
That was fine
That was rubbish