+ Reply to Thread
Results 1 to 2 of 2

Consolidating Data returned from a Table / Form

  1. #1
    Registered User
    Join Date
    03-11-2022
    Location
    Michigan
    MS-Off Ver
    Microsoft Office Standard 2019
    Posts
    1

    Consolidating Data returned from a Table / Form

    Hello,

    As the title states I am wondering how to solve the following problem I have (i haven't tested it out as the results need to be returned to me, but I'd like to know the most efficient way to do this once it is):

    A Team Planner excel sheet was sent out to a team of four people. They're tasked to filter their names in Column I and must fill out the blank cells in Columns D, E, F, and L. I've filled in the color as blue for those columns below the headers.

    Once they fill out each entry assigned to them, they'll email their respective spreadsheet back to me. Once that happens, I'm supposed to take each entry and combine all of them into the original sheet, so all the data filled for each person will be shown together. Essentially, I'm taking data from four different workbooks that were edited from the form, and putting them back in the original form.

    How exactly should I do this? I'm not sure if I'm supposed to use Consolidate in Data Tools, or mess around with Power Query / VBA. Thanks for reading!
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,410

    Re: Consolidating Data returned from a Table / Form

    What format is sent to the team? That is, are they sent the entire list of projects and they only fill in those lines that pertain to them? Can they add items to the table?

    I'll proceed with the assumption that the team gets the full lists and fills in those lines that pertain to them.

    One possible way to manage the system is to establish a "database" where the data is reported to. This will be an Excel file with a specific name in a specific directory. The team needs access to the directory. When a team member fills out the table and wishes to submit it, there will be a button to do this. This action will fill out a cell on a hidden sheet. An on close event will check for this cell and if it is not set, will remind the user to submit the changes. Also when opening the file, if the cell is set then the user gets a warning that the data was already summitted.

    The submission process is merely appending the non-blank rows to the database file. This file can then be used as a data source for your reporting. It may be nothing more than opening it and sorting on task number.

    The VB coding to do this is fairly straightforward.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

+ 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. Filter table data & limit returned to 50 rows
    By nickpavlov in forum Excel General
    Replies: 9
    Last Post: 12-03-2021, 04:17 PM
  2. Consolidating data within a table
    By athompson1298 in forum Excel General
    Replies: 1
    Last Post: 09-17-2020, 10:20 PM
  3. [SOLVED] consolidating table data
    By jrtaylor in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-05-2017, 02:02 PM
  4. [SOLVED] Automation Error - Data Returned from Visible Row Fails to Load to Form Combobox
    By Southfish in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-23-2015, 04:12 PM
  5. Consolidating and converting table data - Help!
    By forbzinator in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-20-2014, 10:46 AM
  6. How to re-organize data being returned from a dynamic form?
    By basils57 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-13-2013, 09:54 AM
  7. Consolidating data in one Pivot Table.
    By Kianuz in forum Excel General
    Replies: 2
    Last Post: 10-23-2009, 10:24 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