+ Reply to Thread
Results 1 to 6 of 6

Update Master Data Sheet from Multiple Worksheets

  1. #1
    Registered User
    Join Date
    04-30-2019
    Location
    Devon
    MS-Off Ver
    Office 365
    Posts
    19

    Update Master Data Sheet from Multiple Worksheets

    Hi All,

    I hope everyone is well and keeping safe. I'm hoping someone could help or point me in the right direction to solve this.
    I have a workbook that has a Dashboard Tab, 12 Month Tabs and a Master Data Tab.
    The aim of the workbook is to track order numbers and invoices and provide a quick search feature for both in the Dashboard.
    My original idea was to not bother with the master data and use XLOOKUP to search an array of tables made up from tables in each month - This was way beyond me and I couldn't get that to work... boohoo!
    I'm now going to use the XLOOKUP on the Master Data sheet, but I don't want users to have to enter duplicate data in the Master Data sheet and the corresponding Month sheet. I need a solution that is either 1 to many (enter data in Master Data) to auto update the Month sheets from the Master Data sheet or many to 1 (enter data into the correct Month sheet) to auto update the Master Data sheet from the Month sheets.

    I've attached the workbook with test data in Jan 2020 tab to test the XLOOKUP before trying to modify the formula to check all the tabs. There is a macro (Clearcells1) assigned to the "Clear Search Box" button.

    If possible I'd like to achieve this without using VBA, but if that's not possible then VBA it is.

    Thanks in advance for looking at this
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Update Master Data Sheet from Multiple Worksheets

    I would add all data in 1 worksheet (e.g. with VBA code) and after that analyse the data with a pivot table.
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.

  3. #3
    Registered User
    Join Date
    04-30-2019
    Location
    Devon
    MS-Off Ver
    Office 365
    Posts
    19

    Re: Update Master Data Sheet from Multiple Worksheets

    Hi oeldere,

    Thanks for your reply. Just to clarify, do you mean - Get rid of all the Month sheets, only have raw data stored in the Master sheet and query that sheet with a pivot table?

  4. #4
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Update Master Data Sheet from Multiple Worksheets

    Yes, that is correct. That is the way I would go.

    Make sure you make a column for the date (month).

  5. #5
    Forum Expert torachan's Avatar
    Join Date
    12-27-2012
    Location
    market harborough, england
    MS-Off Ver
    Excel 2010
    Posts
    4,295

    Re: Update Master Data Sheet from Multiple Worksheets

    In essence I would agree with @oeldere.
    However I have just come back online so you may as well have the benefit of your first approach.
    On the monthly sheets click on last row of table - userform appears - enter data - this goes to monthly sheet and master.
    I have moved master over one column to align with monthly sheets.
    I have not formatted anything, it is only outline principle.
    torachan.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    04-30-2019
    Location
    Devon
    MS-Off Ver
    Office 365
    Posts
    19

    Re: Update Master Data Sheet from Multiple Worksheets

    torachan, that's brilliant. Thank you for this, hopefully somehow I'll be able to pay your help forward to someone one day.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Add or Update Worksheets based on Column Status from Master Data to Another Sheet
    By sanjay.k in forum Excel Programming / VBA / Macros
    Replies: 35
    Last Post: 06-24-2020, 01:37 PM
  2. [SOLVED] Need to create Master sheet to update multiple worksheets
    By Roma1r in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 03-08-2018, 05:44 AM
  3. Array Formula to collate data from multiple worksheets to a master sheet
    By jaredli in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-10-2017, 02:47 PM
  4. [SOLVED] Getting data from multiple worksheets to a master sheet
    By SmoothRider in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 09-09-2014, 10:05 AM
  5. Pull data from specific cells on multiple worksheets onto one master sheet
    By WorkforceMedia in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-07-2013, 03:25 AM
  6. Copying (but not deleting) data from a master sheet to multiple worksheets based on Col A
    By SmartBalance in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-06-2013, 03:09 PM
  7. Populate Data from Master Sheet to Multiple Worksheets
    By redheadedstepchild in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-22-2012, 04:50 PM

Tags for this Thread

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