Any Excel Experts that could help me?

deefadog

Hi all, I hope someone can help me, basically i have 4 separate excel spread sheets with all the same fields in, these will be filled out by 4 different people, the i want to merge them every month and create a graph.

Is this possible?

Thanks in advance for any help.

Cheers

googler

If the sheets are all on your network, and stay in the same place all the time, you could create one master sheet with links to the fields in the remote sheets.

This master sheet would, when you open it, auto update the fields, and you would create the graph from this.

If the remote sheets are being mailed to you, and you're unable to guarantee they'll retain the same file location, then as you open each sheet, copy/paste the data into your master sheet, where you've created 4 worksheets for each of the remote users, and one master worksheet where you add the fields from these 4 and create the graph.

Does this help?

spugzbunny

merge them?

if they are all the same template base then I would just copy paste each page each month into a new spreadsheet. So you would have 4 tabs (1 for each). The create a 5th tab which adds up each of the other 4. You can then create a graph from this.

Asssuming the basic template awlays stays the same, once it's set up you only need to 'copy, paste special, values' into each tab and the summary tab should pick up the new figures to give the new total.

There are more complicated ways of doing it but withe xcel, the simplest option is always the best if it does the job!

spugzbunny

ha - googler beat me too it! Yes I also assumed that the spreadsheets will be emailed to you rather than saved in a set location under set file name

deefadog

Thanks Spugzbunny, yes that's sounds exactly what i need.

Yes all the fields will be the same in each document. The only thing i don't know how to do is on the 5th Tab how do i have the first 4 tabs merged together?

I am searching the web like crazy, but can't seem to find what i am looking for, maybe i am searching wrong, always doing that

Cheers

deefadog

Thanks googler - I swear i never saw your post, just Spugz! LoL

All the 4 Excel sheets can be in one location with short cuts to each desktop!

Is there an example of the master sheet pulling in the data? - That was be amazing!

googler

Have you tried the Excel help rather than 'searching the web like crazy' ...???

Keywords such as 'linking data other worksheets' maybe ....?

However, can we assume your data in each of the four worksheets is in A1, A2, and A3. I assume you want to sum A1 for all four worksheets, and put this into your master sheet (in cell A1, maybe?)

A1 in the master sheet will look like

=Sheet1!A1+Sheet2!A1+Sheet3!A1+Sheet4!A1

You don't have to type it all in manually - enter the = in the master, go to Sheet1, click A1, type +, go to Sheet2, click A1, etc.......

Repeat process for A2 and A3.

Does this help?

deefadog

Thanks, yes it does help, well i'll find out tomorrow in work Lol.

So this method works for sheets in the same document? can it be done from different documents?

Thanks again for the help!

Zazen999

deefadog wrote: »

Thanks, yes it does help, well i'll find out tomorrow in work Lol.

So this method works for sheets in the same document? can it be done from different documents?

Thanks again for the help!

Yes, if they are saved somewhere where the master spreadsheet can access them [in the same place each time].

So on your harddrive or system you could have the 4 documents plus the master; and if any updates are emailed to you, overwrite the original document and when you recalc the master it will pick them up and do a graph/calc for you.

fozzeh

As above...and pivot tables may be good too to make things better for analysis.

deefadog

Thanks for the help guyz, I am not having any luck, i have followed the instructions but all i get in the master is - #VALUE!

Here is the formular for each:

Master column A - ='[Report1.xls]SB'!$A:$A+'[Report2.xls]KW'!$A:$A+'[Report3.xls]SW'!$A:$A+'[Report4.xls]CB'!$A:$A

Any ideas what i have done wrong?

Thanks