Creating a master from 2 seperate spreadsheets

[katieshields]

Hi All,

Once again I am here to pick your brans

I have two spread sheets which I am currently controlling and updating but I keep getting asked to report on both as one, I also get asked to report on them separately. I would like to know if there is anyway I can maintain just one version and a master one with both on update automatically. I have searched and searched websites and threads but the only information I can find is about codes ??? I have no idea about codes so I am hoping there is an easy and simple way for me

[Hurricanefly]

This may not be what you want but its worth a try. Can you not have a master sheet and type (= in say A1) then select sheet 1 cell A1 press Enter then the master sheet would automatically update whenever you type information in sheet1. You would need to copy all the way down your Master sheet to get any additional information but it would update as you type info in to it. If this is not clear let me know or if it is not what you are looking for sorry

[jhren]

A lot depends on the nature of your data, but one method is to use Pivot Table Wizard and specify the data from both sheets for the PT report.

Another much simpler method is to copy the data on sheet1 and paste link on the master. Repeat for data on sheet2.

IMO, each method has its caveats... e.g. PT requires manual refresh, paste link small and it will not account for growing data ranges ...but better options than none at all

[katieshields]

Thanks but these two options leave too much room for human error (which is often know with me). I was hoping there was something excel could automatically do in the background.

[:) Sixthsense :)]

No errors can happen with Pivot Table. Use multiple consolidation range in Pivot Table and you can simply select which sheet report needs to be shown from the pivot drop down.

Refer the below thread for details...
http://www.excelforum.com/excel-form...-into-one.html

[Hurricanefly]

How about a simple macro which you could run each time you update any of the sheets. It could delete all data on the master sheet and copy and paste either one or both of the other sheets on to the master, that way it will always be updated with the new data?

[katieshields]

Something like that would be perfect but I have never worked with macros or codes before. I don't suppose you could point me in the right direction could you?

[Hurricanefly]

No problem, I have about 20 mins before I need to leave, but it should be straight forward delete, copy paste. I'm not sure of the size of you'r spreadsheets so I will post an example of what can be done. You can then edit to the size you need. Unless the columns and rows will vary in size each time? well i'm going to assume one will rite

[katieshields]

That would be brilliant. Thank you

[Hurricanefly]

Try this, the sizes are (A1:H100) at the mo, but as I said you can change those. I can modify it later as it is basic but I just don't have time right now. Hope this helps a little. Send a reply to any modifications etc as I'm working blind as in I don't have a sample of the size of you'r data. Just press the button I added to run the macro. Have a look at the data first so you can see what has happened.

[katieshields]

That has worked, thank you so much. I have even managed to amend it to suit the cells I am copying over

How do I go about learning these codes, macros. Is there an online course you can do?

[Hurricanefly]

I would purchase Vba for dummies by john walkenbach very good starting point and you get all the macros in the book to download. I prefer a book so not sure about online courses but I guess there are plenty.

[Hurricanefly]

Also why dont you play around with the one you have, add two extra buttons one for sheet one and the other for sheet two. Then use the code you have then you can update the sheets one or two or both.

[katieshields]

I'm not sure I appreciate you calling me a dummie haha. I have however just downloaded the book and I will be reading it tonight.

Thank you