+ Reply to Thread
Results 1 to 9 of 9

Creating dynamic master sheet based on value and data from other files

  1. #1
    Registered User
    Join Date
    03-04-2020
    Location
    Cologne
    MS-Off Ver
    Office 365 (Win 10 - Work)
    Posts
    12

    Creating dynamic master sheet based on value and data from other files

    Hey folks,

    I dare to ask you for advice before I even started my litte project

    My goal is to create a master sheet/file which is filled dynamically with data from other excel files.
    These files are all build up on the same template (same sheet and columns names and coordinates - different files names though - surprise ). In sheet "Land Rights" of these files there is column A with a string "yes" or "no". If the value of column A is "no", I want the whole row to be added/displayed in the master sheet as long as nobody changes the value to "yes".
    Hence the size of the master sheet is variable according to the row having "no" in column A from all files which I have to define.

    Since I never had the need to get in touch with VBA and I couldn't think of a solution without... do I need to program a macro in VBA in this case or is there any path to follow with a native functions?

    I'm more than thankful for recommendations and tips.

    Cheers,
    dr. idlebert

  2. #2
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,885

    Re: Creating dynamic master sheet based on value and data from other files

    In "A2"
    =IFERROR(INDEX(your path!B:B,SMALL(IF("No"=your path!$a$2:$a$3000,ROW(TRACKER!$a$2:$a$3000)),ROWS(A$1:A1))),"")

    Confirm by pressing SHIFT+CTRL+ENTER


    atul


    If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.

    Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".

  3. #3
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    863

    Re: Creating dynamic master sheet based on value and data from other files

    Hello dridlebert,


    Here's a VBA option:-

    Please Login or Register  to view this content.
    I've assumed:-

    - All source files and the destination file are in the same folder.
    - All data in all files (source and destination) starts in Row2 with headings in Row1.
    - The destination sheet in the open destination workbook is named "Master".
    - The columns of data in each source workbook are A - M. The same in the destination workbook.
    - The code will also list the source file names in Column N of the destination workbook. You'll need to add a heading (such as "Source WB") in cell N1 of the destination workbook.

    I hope that this helps.

    Cheerio,
    vcoolio.

  4. #4
    Registered User
    Join Date
    03-04-2020
    Location
    Cologne
    MS-Off Ver
    Office 365 (Win 10 - Work)
    Posts
    12

    Re: Creating dynamic master sheet based on value and data from other files

    Thanks both of you for quick response and providing helpful code!
    I decided to go with the VBA option and tried to adjust the code for my specific needs. Unfortunately I was not able to succeed
    It's about time to enroll in a VBA course I guess... what do you guys think how long it will take to write such "easy" scripts by myself?

    In the meantime I'd kindly ask you to adapt the code one more time...

    The required adjustments are
    • relative path for stgP
    • it shouldn't read itself and some other files within the while loop (scan all *.xlsm file except itself and file1.xlsm, file2.xlsm, ...)
    • it needs to copy columns A to AD from the source files
    • put source workbook name in column AE
    • when I run the macro, it should delete all data in Master except row A (headlines). -> More a refresh function than to an add all data to the existing one.

    Thanks guys!

  5. #5
    Forum Expert avk's Avatar
    Join Date
    07-12-2007
    Location
    India
    MS-Off Ver
    Excel 2007 & 2003
    Posts
    2,885

    Re: Creating dynamic master sheet based on value and data from other files

    If you attach sample file with required result it will more helpful both.

  6. #6
    Registered User
    Join Date
    03-04-2020
    Location
    Cologne
    MS-Off Ver
    Office 365 (Win 10 - Work)
    Posts
    12

    Re: Creating dynamic master sheet based on value and data from other files

    Quote Originally Posted by avk View Post
    If you attach sample file with required result it will more helpful both.
    Here is an example of a source file with some test data.
    The destination file is meant to be fill by the macro.
    There are approx. 25 source files, all of them .xlsm.
    The structure of the "Overview Land rights" is all the same.
    Attached Files Attached Files

  7. #7
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    863

    Re: Creating dynamic master sheet based on value and data from other files

    Hello dridlebert,

    Here's the code again with some notes and some adjustments:-
    Please Login or Register  to view this content.
    With this line of code:-

    Please Login or Register  to view this content.
    ensure that the worksheet name is spelled exactly the same in all workbooks.

    I hope that this helps.

    Cheerio,
    vcoolio.
    Last edited by vcoolio; 03-05-2020 at 06:46 AM. Reason: More notes.

  8. #8
    Registered User
    Join Date
    03-04-2020
    Location
    Cologne
    MS-Off Ver
    Office 365 (Win 10 - Work)
    Posts
    12

    Re: Creating dynamic master sheet based on value and data from other files

    Hey vcoolio,

    indeed it helped me a lot. But I'm still not capable of integrating some lines to skip a couple of defined file(name)s which are inside the same folder as all the source files with same extension but are not supposed to be read due to a different structure and content.

    Yes, it feels wrong to ask you one more time to solve my problem and write code for me. I hope to be able to write it by myself very soon

    Have a nice Sunday!

  9. #9
    Valued Forum Contributor
    Join Date
    06-29-2014
    Location
    Australia
    MS-Off Ver
    MSO 365
    Posts
    863

    Re: Creating dynamic master sheet based on value and data from other files

    Hello dridlebert,

    If you have files in the same folder with a different structure and which need to be left alone, then the simplest thing to do would be to create a new folder for these files and move them into the new folder (named differently of course). Thus, in future, should you need to deal with them as with the other files you could use the same code, referencing the different path, assigned to a second button on the Master worksheet.

    Cheerio,
    vcoolio.

+ 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: 2
    Last Post: 07-06-2018, 11:52 AM
  2. Dynamic table based off master sheet
    By cuellar in forum Excel General
    Replies: 2
    Last Post: 05-25-2018, 07:23 PM
  3. [SOLVED] Use of Loop Function for extracting data from Master sheet and creating seperate files
    By AnkitJain in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-17-2016, 04:10 AM
  4. Replies: 1
    Last Post: 09-15-2014, 01:03 PM
  5. Replies: 2
    Last Post: 02-14-2014, 01:39 PM
  6. Creating Multiple Output Files from a Master File and updating data between workbooks
    By EXCEL0429 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-29-2013, 11:54 AM
  7. Replies: 3
    Last Post: 02-28-2012, 02:15 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