+ Reply to Thread
Results 1 to 4 of 4

Select specific rows from separate worksheets and import into master worksheet

  1. #1
    Registered User
    Join Date
    02-09-2015
    Location
    London
    MS-Off Ver
    2013
    Posts
    92

    Select specific rows from separate worksheets and import into master worksheet

    I have a Workbook with several worksheets, each of which contain property data in a series of rows. Each row has a master 'Property ID' to link everything up.

    I have created a separate 'master' worksheet with a drop-down showing all 'Property ID' values. When a user select a Property ID, I would like cells in this master worksheet to become populated with associated values from the separate worksheets.

    Once populated, the user can make changes and hit a 'Save' button, which would then update the separate worksheets with the new values against the associated rows linked by 'Property ID'.

    I have tried to do this myself, but can only aggregate all rows from worksheets (rather than single rows as defined by the 'Property ID').

    Any help would be much appreciated! File attached
    Attached Files Attached Files

  2. #2
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    13,122

    Re: Select specific rows from separate worksheets and import into master worksheet

    One way...
    Please Login or Register  to view this content.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember...Mark Thread as Solved.
    Excel Forum Rocks!!!

  3. #3
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Select specific rows from separate worksheets and import into master worksheet

    You could do it with a few formulae, as follows:

    B3: =IFERROR(INDEX(Lease!$A:$E,MATCH(B$1,Lease!$A:$A,0),ROWS($1:1)),"")

    Copy down into B4:B7, and format B7 as a date.

    B8: =IFERROR(INDEX(Financial!$B:$D,MATCH(B$1,Financial!$A:$A,0),ROWS($1:1)),"")

    Format as currency, then copy into B9 and B10.

    B11: =IFERROR(INDEX(Headcount!B:B,MATCH(B$1,Headcount!$A:$A,0)),"")

    Hope this helps.

    Pete

  4. #4
    Registered User
    Join Date
    02-09-2015
    Location
    London
    MS-Off Ver
    2013
    Posts
    92

    Re: Select specific rows from separate worksheets and import into master worksheet

    Thanks - this is a great formula solution. Any idea how I could then use VBA to write those values back to the worksheets, only replacing values in rows containing the Property ID?

+ 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. Import Macro to import separate files to separate worksheets - deletes existing worksheets
    By Hondahawkrider in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-01-2015, 02:24 PM
  2. 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
  3. [SOLVED] Automatically paste $ data from 1 master worksheet to 100's of separate worksheets by name
    By Frasterist in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 04-17-2013, 01:28 PM
  4. 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
  5. 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
  6. Replies: 3
    Last Post: 11-22-2010, 10:02 AM
  7. Merging specific Worksheets into one Master Worksheet
    By dagindi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-24-2009, 07:34 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