compiling information from multiple excel documents to a MASTER

sikamon

New Member
Joined
May 18, 2011
Messages
5
Hi,

if anyone could help on the following problem i would really appreciate it:

I have about 15 status reports that come in each month from around the country, now each report is spread across 4 sheets in excel. it has information about expenses, time off, amount of people, and text boxes filled in with reports.

this report that comes in will always have differnet values but the format should be the same, i would like a way of collating the data, such as amount spent on fuel( part of expenses) and budget into a bigger picture file, for the entire fleet, so i could just open one master document and have it show me, for instance the total budget for the 15, this would obviously come from the 15 indiviual reports, these are all emailed in throughout the month.

i hope i've explained clearly enough so far.

So if anyone could help, what i would like is the easiest way to take data from one excel document and put it into another document, as it comes in, in the most effecient way, rather than just copy and pasting all the seperate bits of information and putting them into the new document then adding up for all 15 reports.

which at present it takes about an hour to take all the information across.
a more effecient method would be highly appreciated


Thanks

Simon
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Have a master spreadsheet of 16 tabs - one for each report and a frontpage for whatever MI you want to accumulate.

As the reports come in, paste them into their relevant tab.

You can then use formulae to get the data you want into the places you want it on the frontpage and add bells, whistles, frills & formatting as desired..

Once it's been made the first time (would admittedly take a couple of hours), should be two minutes to produce per month.
 
Upvote 0
Hi,

Thank you for the reply. i love the idea, and this is the approach i am going along, but in the reports that come in, there is 4 seperate sheets, with some data some texts boxes etc, i dont really see how i can put this onto one page on the master copy.

and there is probably 50 or 60 bits of data that need to be copied across, i looked into a macro that i could make that was a simple mouse click follower so i could copy all the information from the sheets and paste as needed and then save it as a button for "press to copy all information"
but its well out of my league.
 
Upvote 0
Put a front page on the 4 pager that has all the data you want. Paste that in to your 16 pager.

Advantage is that the dashboard then enables your users to see what they're being reported on...assuming that's an advantage!

Edit...the macro thing works too, and shouldn't be too difficult....

Save your incoming files into a specific folder, with specific names.

Then record a macro as you navigate to that folder, open one file, copy the data from it and close the file.

Stop recording, and copy the resultant macro 14 times, changing the filename each time.
 
Last edited:
Upvote 0
thanks for all the help so far.

im experiencing a problem now, where on this "front page" all the information is linked to display it as i'd like i.e. =Sheet1!A65
that displays £24000 then when copied to the new document,
it displays nonse, from =[MSR.xls]Sheet1!A52 why would this change? and how do i avoid it?

thanks
 
Upvote 0
ok, so i have seen from searching the forums that using paste special and selecting values copies across the correct values.

is there any way to make this the default setting for my master file only?

so that people could just press Ctrl + v and it would paste values only rather than linked cells?

thanks
 
Upvote 0

Forum statistics

Threads
1,224,616
Messages
6,179,909
Members
452,949
Latest member
beartooth91

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top