+ Reply to Thread
Results 1 to 8 of 8

How to automatically transfer data from "Master Summary" worksheet to designated worksheet

  1. #1
    Registered User
    Join Date
    03-13-2016
    Location
    Southern State
    MS-Off Ver
    Office 2010 & 2013
    Posts
    26

    How to automatically transfer data from "Master Summary" worksheet to designated worksheet

    Is it possible to maintain One Master worksheet and upon specific entries, automatically enter data (row) into the appropriate worksheet without using macros and vba? Our company security will not allow me to save a document with macro/vba.

    I would like to be able to maintain only one master worksheet which includes invoice number, date, vendor, etc. When a specific vendor invoice is entered, add entry to Vendor Worksheet, ie. Vehicle maintenance (1 worksheet), Fuel (1 worksheet), etc.

    Thank you very much for any assistance/suggestions you may offer.

    UPDATE: Example attached
    My hope is to enter each of my invoices with all pertinent information into one Master Data section.
    1) If vendor matches criteria (Fleet,Fuel,Temp) I will automatically complete additional cells at the far right of worksheet (Master)
    2) Once data is entered, the information will be added automatically to the appropriate worksheet tabs, as well.
    3) I'll maintain one master worksheet with all data but it will also be available on their own worksheet.

    I am most likely making this more complicated than it should be. The fact is...I'm spending valuable time entering this data on multiple spreadsheets when I feel it's totally unnecessary.

    I appreciate any assistance that may be offered and your candor if I'm backwards in my "thought" process.

    Thank you.
    Attached Files Attached Files
    Last edited by tmac1985; 04-08-2018 at 09:24 AM. Reason: Attachment added

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Master Worksheet

    Hi tmac,

    Why not put all in the master worksheet and then filter the one table to show what a single sheet would show? Have you looked at AutoFilters in a table yet?

    https://support.office.com/en-us/art...6-27B932E186E0
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Registered User
    Join Date
    03-13-2016
    Location
    Southern State
    MS-Off Ver
    Office 2010 & 2013
    Posts
    26

    Re: Master Worksheet

    I have used filters, so maybe I'm overthinking this. I did watch the video link you provided.
    Thank you for your suggestion.

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Master Worksheet

    Thanks for the title change
    Last edited by FDibbins; 04-07-2018 at 02:05 AM.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to automatically transfer data from "Master Summary" worksheet to designated works

    What you want can probably be done with something like INDEX/MATCH or an array INDEX/SMALL/IF combo. But to be more specific, we would need to see what you have and what you want. Can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

    To attach a file to your post,
    click advanced (next to quick post),
    scroll down until you see "manage file",
    click that and select "add files" (top right corner).
    click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.

  6. #6
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: How to automatically transfer data from "Master Summary" worksheet to designated works

    How about PowerQuery? (no formula, no vba)

  7. #7
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: How to automatically transfer data from "Master Summary" worksheet to designated works

    You are asking to match vendor column with sheet names that dont match. What is the logic on what goes where?

    If there are more than 1 vendor per sheet (the different Auto' for instance), would you be open to adding a helper column to help ID them, so they can be bright across?
    Alsom, your headings from Master to (at least) Fleet, dont all match - you need to make sure the headings are identical.

    This ARRAY formula is kind of what I would be looking to use, but it is set for A/S A only....
    =INDEX('Master Data'!$A:$AC,SMALL(IF('Master Data'!$D$3:$D$9="Auto Service A",ROW('Master Data'!$A$3:$AC$9)),ROWS($A$1:$A1)),MATCH(A$1,'Master Data'!$A$2:$AC$2,0))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

  8. #8
    Registered User
    Join Date
    03-13-2016
    Location
    Southern State
    MS-Off Ver
    Office 2010 & 2013
    Posts
    26
    Quote Originally Posted by FDibbins View Post
    You are asking to match vendor column with sheet names that dont match. What is the logic on what goes where?

    If there are more than 1 vendor per sheet (the different Auto' for instance), would you be open to adding a helper column to help ID them, so they can be bright across?
    Alsom, your headings from Master to (at least) Fleet, dont all match - you need to make sure the headings are identical.

    This ARRAY formula is kind of what I would be looking to use, but it is set for A/S A only....
    =INDEX('Master Data'!$A:$AC,SMALL(IF('Master Data'!$D$3:$D$9="Auto Service A",ROW('Master Data'!$A$3:$AC$9)),ROWS($A$1:$A1)),MATCH(A$1,'Master Data'!$A$2:$AC$2,0))

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Thank you for pointing out the issues with headings not matching. I was trying to have the sample file uploaded rather quickly for a general idea.
    On fleet worksheet, there will be multiple entries. End result will track maintenance on all vehicles (~30 vehicles) by VIN. I could have a helper column. I could also have all VIN numbers in a list for look up or a special ID number.

    I will try to work on this tomorrow. Your help and patience is appreciated. Thank you.

+ 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. Replies: 0
    Last Post: 02-01-2018, 04:52 PM
  2. Macro to copy data from multiple worksheet cells in to a single Master worksheet
    By thunt13 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-08-2015, 03:24 PM
  3. [SOLVED] Macro to Copy Master Worksheet create and paste in new auto named worksheet
    By TheRealLethality in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-15-2014, 12:46 AM
  4. Merging multiple excel files in a folder into one master worksheet in a new worksheet
    By johnny_canuck in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 08-22-2013, 12:20 PM
  5. Drop Down List to Copy Entire Contents of Worksheet into Master Worksheet
    By mrmartin in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-14-2011, 10:54 PM
  6. Multiple Worksheet to gather in Master Worksheet minus one Wroksheet
    By Navymagirl in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-28-2010, 12:09 AM
  7. Copy an existing worksheet and carry totals to a master worksheet
    By adore_r in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-31-2008, 12:02 AM

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