+ Reply to Thread
Results 1 to 14 of 14

Creating a master from 2 seperate spreadsheets

  1. #1
    Forum Contributor
    Join Date
    02-24-2013
    Location
    Dubai, UAE
    MS-Off Ver
    Excel 2013
    Posts
    113

    Creating a master from 2 seperate spreadsheets

    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

  2. #2
    Forum Contributor
    Join Date
    05-09-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    259

    Re: Creating a master from 2 seperate spreadsheets

    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
    Last edited by Hurricanefly; 06-03-2013 at 01:31 PM. Reason: Not clear enough I don't think

  3. #3
    Valued Forum Contributor
    Join Date
    05-20-2013
    Location
    Ohio, USA
    MS-Off Ver
    Excel 2013
    Posts
    941

    Re: Creating a master from 2 seperate spreadsheets

    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

  4. #4
    Forum Contributor
    Join Date
    02-24-2013
    Location
    Dubai, UAE
    MS-Off Ver
    Excel 2013
    Posts
    113

    Re: Creating a master from 2 seperate spreadsheets

    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.

  5. #5
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,771

    Re: Creating a master from 2 seperate spreadsheets

    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

  6. #6
    Forum Contributor
    Join Date
    05-09-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    259

    Re: Creating a master from 2 seperate spreadsheets

    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?

  7. #7
    Forum Contributor
    Join Date
    02-24-2013
    Location
    Dubai, UAE
    MS-Off Ver
    Excel 2013
    Posts
    113

    Re: Creating a master from 2 seperate spreadsheets

    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?

  8. #8
    Forum Contributor
    Join Date
    05-09-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    259

    Re: Creating a master from 2 seperate spreadsheets

    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

  9. #9
    Forum Contributor
    Join Date
    02-24-2013
    Location
    Dubai, UAE
    MS-Off Ver
    Excel 2013
    Posts
    113

    Re: Creating a master from 2 seperate spreadsheets

    That would be brilliant. Thank you

  10. #10
    Forum Contributor
    Join Date
    05-09-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    259

    Re: Creating a master from 2 seperate spreadsheets

    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.
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    02-24-2013
    Location
    Dubai, UAE
    MS-Off Ver
    Excel 2013
    Posts
    113

    Re: Creating a master from 2 seperate spreadsheets

    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?

  12. #12
    Forum Contributor
    Join Date
    05-09-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    259

    Re: Creating a master from 2 seperate spreadsheets

    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.

  13. #13
    Forum Contributor
    Join Date
    05-09-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    259

    Re: Creating a master from 2 seperate spreadsheets

    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.

  14. #14
    Forum Contributor
    Join Date
    02-24-2013
    Location
    Dubai, UAE
    MS-Off Ver
    Excel 2013
    Posts
    113

    Re: Creating a master from 2 seperate spreadsheets

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1