+ Reply to Thread
Results 1 to 5 of 5

Auto populate main worksheet info to appropriate secondary tabs based on info in column D

  1. #1
    Registered User
    Join Date
    05-24-2013
    Location
    London, ON
    MS-Off Ver
    Excel 2007
    Posts
    43

    Auto populate main worksheet info to appropriate secondary tabs based on info in column D

    I have to create a workbook containing 8 sheets. The first sheet is the master sheet with a listing of all open files and who is handling the file. The remaining 7 sheets are the names of the people who are handling the file and a breakdown of all the files they currently have according to the master sheet.
    I want the user to be able to input data on the master sheet "FR-Directory" and have the data auto populate to the individual sheets according to who is handling the file "Handling File" (Column D) as well as keep it in alphabetical order according to "Surname" (Column A).
    I have also added an auto sort feature to the master sheet "FR-Directory" that will sort the data alphabetically once the user has selected a different tab. I wanted the data to sort once saved, but was unable to get that feature to work so for now we will have to remember to activiate the tab by selecting a different tab and then go back....unless someone has a better idea?
    I have attached a sample of the file I am currently using.
    Any help is greatly appreciated
    Attached Files Attached Files

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Auto populate main worksheet info to appropriate secondary tabs based on info in colum

    Not the prettiest version, but it gets it done if you just hide the 3 helper columns.

    This alphabetizes:
    IF(A2<>"",SUMPRODUCT((A2>=$A$2:$A$406)+0)+1,"")

    This ranks by Handle and Alphabet:
    =COUNTIF($D$2:$D$406,D2)-((SUMPRODUCT(($D$2:$D$406=D2)*($G$2:$G$406>G2))+1)+COUNTIFS($G$2:G2,G2,$D$2:$D2,D2)-1)+1

    And this recalls each record by Handle and overall rank:
    =IFERROR(IF(INDEX('FR-Directory'!$A$2:$E$406,MATCH(1,($F2='FR-Directory'!$H$2:$H$406)*($F$1='FR-Directory'!$D$2:$D$406),0),COLUMN(A1))=0,"",INDEX('FR-Directory'!$A$2:$E$406,MATCH(1,($F2='FR-Directory'!$H$2:$H$406)*($F$1='FR-Directory'!$D$2:$D$406),0),COLUMN(A1))),"")


    I adjusted the VB in this sample to accommodate the rank of the helper columns. (out to column H)

    autosort for jingles.xlsm

  3. #3
    Registered User
    Join Date
    05-24-2013
    Location
    London, ON
    MS-Off Ver
    Excel 2007
    Posts
    43

    Re: Auto populate main worksheet info to appropriate secondary tabs based on info in colum

    At first glance this seemed to be working, but once I added the actual list of open files (405 in total so far) it only copied over the first 36 files to each of the individual tabs. Any idea as to why it is only recognizing the first 36 files and not the entire list?

  4. #4
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Auto populate main worksheet info to appropriate secondary tabs based on info in colum

    Because you only had formatted the first 36 rows of each Handler's tab.

    Pick a handler's tab, such as SM.

    The numbers in F needs to count past row 37, so extend increment them down to 150 or so. Then copy the formulas in A:E down that far too.

    In this version, I've extended each Handler tab down to row 200.

    autosort for jingles.xlsm

  5. #5
    Registered User
    Join Date
    05-24-2013
    Location
    London, ON
    MS-Off Ver
    Excel 2007
    Posts
    43

    Re: Auto populate main worksheet info to appropriate secondary tabs based on info in colum

    This works perfectly. Thank you so much daffodil11

+ 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. Looking to auto-populate column and row info for values...
    By someguy12341 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-01-2013, 12:46 PM
  2. Replies: 0
    Last Post: 02-18-2013, 03:47 PM
  3. Can a macro change data on tabs when info in F7 of Job Info worksheet changes? If so How?
    By ILoveStMartin in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 12-05-2012, 02:59 AM
  4. how to make multiple sheets based on info in main worksheet
    By lunima in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-09-2012, 12:43 PM
  5. Replies: 6
    Last Post: 08-12-2010, 03:53 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