+ Reply to Thread
Results 1 to 6 of 6

Populating a master database based on separate worksheets

  1. 09-17-2018, 01:15 PM #1
    bkdre
    bkdre is offline
    Registered User
    Join Date
    09-13-2018
    Location
    United States
    MS-Off Ver
    MS Office 2016
    Posts
    6

    Thumbs up Populating a master database based on separate worksheets

    Hi everyone! You guys have saved my skin in the past, so thanks again for a great community of support for a noob like me

    Problem: I am building a master database that will compile completion dates for each phase of a project life cycle. I'm having trouble with an iterative formula that would pull the date for each phase of a process for each specific project.

    Specifically, each project has it's own tab, and I'd like the respective dates for each phase to pull into the master compiled list. The final product would be where you input the worksheet NAME (for a single project), then the dates are all filled into the master database, based on the correlating categories

    I've been working through trying to use Index Match Vlookup functions, but haven't had much luck.. I used to have a similar worksheet at my previous work, but can't seem to replicate.

    Could someone please look at the attached excel? The problems I've mentioned above are in the attached, which may help visualize what I'm trying to accomplish.

    Thanks in advance!
    Attached Files Attached Files
    Last edited by bkdre; 09-17-2018 at 06:34 PM.
    Reply With QuoteRegister To Reply
  2. 09-17-2018, 01:22 PM #2
    63falcondude
    63falcondude is offline
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Populating a master database based on separate worksheets

    Try these:

    B7 =INDIRECT("'"&A7&"'!B2")
    D7 =INDIRECT("'"&A7&"'!B7")
    E7 =INDIRECT("'"&A7&"'!B8")
    F7 =INDIRECT("'"&A7&"'!B9")
    G7 =INDIRECT("'"&A7&"'!B10")
    H7 =INDIRECT("'"&A7&"'!B11")

    Drag all formulas down as far as needed.
    Last edited by 63falcondude; 09-17-2018 at 01:29 PM. Reason: Corrected where formulas should go
    Reply With QuoteRegister To Reply
  3. 09-17-2018, 01:29 PM #3
    63falcondude
    63falcondude is offline
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    365
    Posts
    6,266

    Re: Populating a master database based on separate worksheets

    Alternatively, you can use this in D7:

    =INDEX(INDIRECT("'"&$A7&"'!$B$7:$B$44"),MATCH(D$5,INDIRECT("'"&$A7&"'!$C$7:$C$44"),0))

    Drag the formula to the right and down as far as needed.
    Last edited by 63falcondude; 09-17-2018 at 01:36 PM. Reason: Corrected INDEX range
    Reply With QuoteRegister To Reply
  4. 09-17-2018, 02:36 PM #4
    bkdre
    bkdre is offline
    Registered User
    Join Date
    09-13-2018
    Location
    United States
    MS-Off Ver
    MS Office 2016
    Posts
    6

    Re: Populating a master database based on separate worksheets

    63falcondude you're the man. It's working, thanks! But I think I'm looking for ways to make it more dynamic.

    For example, if I want to add more steps into a project's process, I'd have to manually switch up the B2 B7 B8 etc references. I'm thinking maybe an index or match function with "elevator" reference numbers that are easy to alter if I need to add or change anything in the original process... let me know if you think there's a better way to make it more dynamic.

    Thanks again
    Reply With QuoteRegister To Reply
  5. 09-17-2018, 02:48 PM #5
    bkdre
    bkdre is offline
    Registered User
    Join Date
    09-13-2018
    Location
    United States
    MS-Off Ver
    MS Office 2016
    Posts
    6

    Re: Populating a master database based on separate worksheets

    Apologies, my page just refreshed and I'm seeing your response. This is exactly the kind of dynamic function I was looking for, thank you!

    However, what if I end up having the same step name in different stages of the process? Example: a "Negotiate" step in the "Prelim" stage, then a "Negotiate" step in the "Wetland" stage? I'll need to figure out a way to differentiate between each stage of the process.. hmm. Let me know if you have any ideas

    Thanks!
    Reply With QuoteRegister To Reply
  6. 09-17-2018, 06:33 PM #6
    bkdre
    bkdre is offline
    Registered User
    Join Date
    09-13-2018
    Location
    United States
    MS-Off Ver
    MS Office 2016
    Posts
    6

    Re: Populating a master database based on separate worksheets

    I think I figured it out- I'll share my results in case anyone sees this / needs this in the future.

    It might be a nontraditional workaround, but I made an "elevator" row in the master tab that corresponds to column E in each perspective project. The data in rows 8 and 9 in the master database now pulls from each respective project sheet. Not sure if it's the best way to do it, but it solves the issue with repeat criteria names (see attached the excel).

    Happy to hear other suggestions, but this seems to work! Thanks again 63falcondude for the guidance
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. VLOOKUP? Populating multiple rows/columns of data onto separate pages from master page.
    By rebeccabonville in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-04-2017, 01:19 AM
  2. Separate 1 master list into 2 other worksheets according to key words in excel.
    By AND-REE-UHH in forum Office 365
    Replies: 2
    Last Post: 09-12-2015, 07:54 PM
  3. Get Master Worksheet to Separate Data into Various Worksheets
    By NewbieOfVBA in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-18-2014, 04:30 PM
  4. Info from 6 different worksheets populating one Master sheet
    By Designia in forum Excel General
    Replies: 2
    Last Post: 10-23-2013, 05:10 PM
  5. separate the data from the 25 entries on the master to separate worksheets
    By nemo12 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-21-2012, 09:27 AM
  6. Populate Master Worksheet from 5 Separate Worksheets
    By MoSeriouS in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-29-2011, 10:46 AM
  7. Separate Data from Master Worksheet into Individual Worksheets
    By bhenderson in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-26-2011, 02:14 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1