Split data into multiple tabs

{ 326 Comments }

By Ashish Mathur · · · Leave a comment

Assume a four column database with department as the second column.  Entries in the department column may be repeated.  A person may wish to create one worksheet tab for each department.  Therefore, as and when a person names a worksheet tab as one of the departments, all rows of that department should appear on this new sheet.

You may refer to my solution in this workbook.

You may also refer to a similar article at the following link

Leave a Comment

Your email address will not be published.

CAPTCHA

*

  • Could you please help me to expand the table/range to A1 through Z1414 and adjust the formulas to encompass the entire table/range? Thank you!!

    Reply

    • Hi,

      Your question is not clear. Please upload your file to SkyDrive or any other fee file hosting site and then share the link of the uploaded file here. Please also mark the cells where you need my help.

      Reply

  • Hello,

    I’m trying to create the same thing but with a six column database and four tab categories. I’m unsure what code you’ve used to automate it and how I can expand it to fit my database. Any help would be greatly appreciated!

    Reply

          • Hi Ashish, thank you for sharing the workbook with us, it has been of incredibly helpful for me.
            I’m using it to organise the referrals to the service where I work, which are assigned to the several disciplines in the team, building up waiting lists for each discipline.

            There’s only one flaw I can find at the moment:
            When elements of the first row for the same discipline are deleted from the master sheet, so are also the following rows in the discipline sheet.

            I’ll try to give you an example. In the Master sheet I have:

            Client 1 – Nursing
            Client 2 – OT
            Client 3 – Nursing

            In the Nursing sheet I will have Client 1 and 3. If Client 1 gets deleted from Master sheet, Client 3 will not appear on Nursing sheet anymore. Basically if row is left blank, it will affect following rows in the connected sheets.
            Is there any way to correct that?

            Thank you!

          • You are most welcome. I cannot visualize the problem. Share the link from where i can download your workbook and show the problem clearly.

          • Hi Ashish, thank you for your help. I couldn’t reply your last message, so I’m adding my response here, so that you can easily read my previous message. As you can see from the file, nothing is in the “nursing” tab, but if the first row gets selected, all other row will appear again.
            It’s not a huge problem, but most of my colleagues are not very confident excel users and some of them barely know how to use a computer. If there’s no solution, I’m still ok with it.

            https://onedrive.live.com/edit.aspx?cid=479e33a0d6f51a32&page=view&resid=479E33A0D6F51A32!1526&parId=479E33A0D6F51A32!1449&app=Excel

          • I’ve just noticed that the same problem happens in your file but only in the “Costume” tab.

          • Hi,

            In cell A2 of the Nursing sheet, enter this array formula (Ctrl+Shift+Enter) and copy down/right

            =IFERROR(INDEX(Referrals!$A$2:$J$550,SMALL(IF(Referrals!$G$2:$G$550=MID(CELL(“filename”,O1),FIND(“]”,CELL(“filename”,O1))+1,256),ROW(Referrals!$G$2:$G$550)-ROW(Referrals!$G$1)),ROW(1:1)),MATCH(A$1,Referrals!$A$1:$J$1,0)),””)

            Hope this helps.

  • Thank you, this is just what I needed. Now I can see the formula too, I was thinking it had to be a VBA Code….Thanks for all of your help!

    Reply

  • I had one question regarding the formula’s you are using in each tab to pull the cell data; what is the purpose of the O1 in the function CELL(“filename”,O1)? Which then becomes P1, Q1 etc…

    Reply

  • Hi.

    I have same type of file as Lynette. But don’t want to use the dropdown list.
    I need the file to extract data based on different criteria.

    Its a client list that have all different types of clients with different types of taxes etc. I need to to so show the different clients/taxes on separate sheets so that I can add details to it on the sheets.

    Ex. All the clients with ID numbers should display on the ID sheet. (Only the names) The I need to fill in / tick of as work is finished on the ID sheet. So the data on the ID sheet. The data should be pulled through alphabetically on each sheet.

    So if think it criteria is if there is a value or not blank (in each column) then it should include into the different sheets!?!

    PS. The main sheet changes/grows constantly so new rows will be added regularly. So the range (I think should be dynamic – I think).

    I need the formulas cause I might be able to figure it out if i need to add a sheet or a column to the main list. I it is code my brain will go into a continuous loop and burn out!

    Please help!!!

    I’ve made all cell red the need data from the main sheet!

    http://sdrv.ms/ZiUN75

    Reply

    • Hi,

      You may refer to my solution in this workbook. I have shown the formula for the first three sheets – ID, Reg No and Tax. Please do it yourself for the other sheets. For the formula to work in column B of the other sheets, the following two conditions should be satisfied:

      1. The tab names should be the same in spelling as the headings in row 2 of the ALL sheet
      2. You will have to write a formula in cell AN1 of the other sheets (Just copy the formula from cell AN1 of Tax sheet and paste it in cell AN1 of the other sheets). The formula in cell AN1 will basically print the tab name. This tab name is then being used in the formula in column B.

      As and when more rows of data are added to the ALL sheet, the ranges in column B formulas of all sheets will expand. This will happen because the range on ALL sheet has been converted into a Table.

      Hope this help.

      Reply

  • Hi Ashish,

    Thanks for your help.

    https://skydrive.live.com/redir?resid=D853D8EDDF08F80E!109&authkey=!AI1DxP-iP9FHKtw

    Here is the link to skydrive file.

    If you view the Sydney, Perth, Brisbane, Adelaide tabs you can see that they are being filled automatically from the master page. based on location.
    My problem is the gaps between the results.
    I would like it to fill the subsequent tabs from top down without gaps based on the location and will update automatically when new data is entered on to the master page.

    Your help is great thanks.

    Gary

    Reply

  • Hi Ashish,
    Just a follow up question for you. I am using your formula into new sheets. I am beginning to add more tabs with more cities i can not find the part of the equation that shows on what basis you are sorting the details into the new tabs.
    For Instance if i Add a new tab named Newcastle and will enter the information on the master and the location will have Newcastle. Where do i change the formula to account for this new info?

    Reply

    • Hi,

      Try this

      1. On your Master sheet, add rows for Newcastle
      2. Right click on any existing tab (which already has the formula for pulling data from the Master sheet) and select Move or Copy > Create a Copy
      3. Rename the newly created tab as Newcastle

      Data for Newcastle should automatically appear in the Newcastle tab.

      Hope this helps.

      Reply

  • Ashish, using the same format on this workbook, is there a way to have one name showing in two different tab/departments?

    Reply

    • Hi,

      What is the basis for showing Felipe Martinez on the Active tab – please clarify i.e. what on the Master can prompt the formula to show Felipe Martinez on the Active sheet also?

      Reply

      • Thanks Ashmish, So if possible, the Master will feed the all of the tabs, Master will include name, EID, DOH, SSN Address and supervisor amongst some. Active will contain information like Name, EIP and supervisor, Occurrences just name and supervisor and so on. I hope this makes sense. I have attached an updated workbook of how is going to look if possible. hope you can help.

        https://skydrive.live.com/redir?resid=29E1C6FA26DCCFA1!131

        Reply

  • Hello,

    To answer your questio, The last 2 tabs are “Occurances”, for when someone is late, and “Corrective Action”, for when someone receives a corrective for whatever reason.
    What I need is for those tabs to auto populate with information within the Master tab, similar to the “Active” and “Terms” tab within Those tabs.
    The specific information that I need to auto populate in the 2 tabs would be 1. Name 2. EEID 3. Supervisor 4. SDM 5. DOH.

    Please let me know if this helps.

    Thank you,
    Felipe Martinez

    Reply

  • Well, i was having some light troubles setting up a pivot table so it would automatically expand to new entries whilst staying searchable and sortable, and this post and the example sheet helped me quickly figure out that i was merely applying the pivot table conversion wrongly (i was trying to use the pivot table conversion with headers that had enumeration, i.e “1. introduction” and “2. building layout” rather than just the text entry).

    Thanks to Ashish for the answers here and on MS Q&A, as well as the example sheet, which helped me quickly realize the issue i was having.

    Regards
    Michael

    Reply

  • How can i type information into excel spreadsheet a,b,c and have it transfer into box 4x in spreadsheet d?
    for text, not numbers

    Reply

  • Hello! This is the answer to my question that multiple excel forums cannot provide!

    Can you also help formulate my tabs in the same manner that I want to split my data as per the account? How can I attach my file, by the way?

    Reply

  • Hi Ashish,

    could you help me separate the data in master sheet to 12 tabls namely based on months on two criteria
    Criteria 1- match the ID expiry month – Whole row Except the cells related to Due information to be copied to the tab with month name for rows that match the expiry month
    Criteria 2 – Match the Due month – whole row except cells related to expiry information to be copied to the tab with month name for rows that match due month

    I have uploaded the file to sky drive
    https://skydrive.live.com/redir?resid=D2B77634AADCB95D!165&authkey=!ADtSpn9c_JwEFmE

    Reply

    • Hi,

      On the Feb sheet, why is Employee 12 appearing twice? Also, on the Mar sheet, why is Employee 31 appearing twice? There is a similar problem on the April sheet as well. Are these mistakes?

      Also, I will have to do this via a VBA code. I hope you will be OK with a VBA solution.

      Reply

      • it is appearing twice as I did both manually. one sort for expiry of Id and another sort for Due. you will see in the first time only fields related to ID expiry is filled in and cells related to due month are left blank and second time it is vice verse. if both criteria matches and all data comes in same row it is fine with me too.

        Even though I am not familiar with VBA code i hope it should be OK

        Reply

          • splitting in to multiple tabs are working fine but the data filling is not as per what I requested, may be I didn’t explain it well. in the sheet if expiry column (Column K) is FEB , then columns I & J corresponding to rows that have expiry as month FEB in work sheet Feb . Similarly Columns L&M should only have data corresponding to rows that have Due month (In column N) February.

            In other words taken any row data will be only filled in columns I & J if ID expiry month (column K) is matching the worksheet name and columns L&M will be only filled in if the due month (Column N) is matching the work sheet name. In any row all columns I, J, L &M will only be filled in if both Expiry (Column K) and due (Column N) is same as the name of work sheet.

  • Perfect. You are the expert!!!!!!

    i have asked lots of people about this and you are the first to give me a solution

    Thanks very much for all the help

    Reply

  • Ashish

    I have a Workbook that I want to use to record club memberships. I have a master sheet (called All Members) that I will use for capturing all the relevant information for each member. However I would like each members details (entire row) to be copied to the relevant shhet based on their membership category. Their categories will be selected using a drop down list on column J.

    For example: If I enter a new member and they are a Life member (selected from column J) then their details will be copied to the relevant sheet (named Life).

    I have uploaded the basic workbook to SkyDrive at: http://sdrv.ms/IE0gjy

    I’d really appreciate it if you could help me with this.

    Thanks

    Reply

  • Hai Ashish Mathur

    it’s very useful sir for my self now. but am cann’t understanding this formula.
    Little bit tough to understand this code, what going there.
    did you explain that formula as briefly..
    is there any Macro for this. it will may easy to understand for every one.
    am waiting for your answer.

    Any how thank you for this

    Reply

  • The workbook purpose is to have a summary of some interviews done.

    I have a workbook made of several sheets:

    – the first sheet called “ALLADDQ” will serve a summary and will contain a table made of 7 columns. The header of those columns are
    Column A: Interviewer
    Column B: Number
    Column C: Interview Number
    Column D: Full Name in English
    Column E: Full Name in Arabic
    Column F: Remarks
    Column G: Date of the interview

    – the 40 sheets have all the same format and header. They have also the same columns of the summary sheet (and I will be adding other columns to those). Those will be the interviewer sheets

    What will happen is that this workbook will be shared on a network between different users. Those will enter the information on the “AllADDQ” sheet. What I need to happen is to have each row figure on one of the 40 sheets based on the information present in the first column. Meaning, I need to have for each interviewer, the details of only his interviews figuring…

    I do not know why the link is giving blank, though when I open it, the data is figuring. Hope that I was able to explain it clearly… Can you send me a workbook containing the ALLADDQ Sheet, and one of the interviewers sheet so I make 39 copies of the last sheet?

    Thanks a lot for your help,

    Reply

    • Hi,

      You may refer to my solution in this workbook

      Just create a copy (Right click on the tab > Move or Copy > Create a Copy) of the softball worksheet and change the tab name to Basketball. All data on the basketball sheet should automatically change.

      Hope this helps.

      Reply

  • I submitted previously (April 2014), and have not been able to figure out why I am only receiving 0 in my formula cells on the additional worksheets.

    Here is a link to my workbook, less my attempts at the formula on the worksheets that should only contain the company’s employees that are listed on the MASTER –
    http://1drv.ms/RkzRMk

    I’d appreciate any help you can extend to my issue (although you have in previous posts, I am having a brain block on where I have mistyped in the formula).

    Thank you
    Shannon

    Reply

      • Yes, your solution worked fantastically for the first 30 worksheets after the Master worksheet (total of 31 worksheets in file), but I am unable to get my 32+ worksheets to show the data in the cells. The sheet is a COPY of a functioning worksheet and even displays the column headings, but does not show the data from the master worksheet. Any advice? In the future, I will have additional data (companies) to add to the master worksheet and ergo, will be adding more worksheets to the file. I hope you can direct me in the direction I need to head because I am stumped right now!

        Reply

        • Hi,

          I cannot tell what the problem is unless I see the workbook. Upload the file with 32+ worksheets and let me know which worksheet(s) you are facing a problem with.

          Reply

          • Sorry for the delay (I work PT). Attached is the workbook I am having issues with. http://1drv.ms/So0qkn

            And here is my issue: The data located in the Master Sheet, rows 292 to end of data does not transfer/copy to it’s appropriate/assigned worksheet. Each worksheet has been copied from the previous worksheet of itself and/or copied from functioning worksheet. I tried what I could to find issue. I am able to add new employees, new companies (ie: ABC, Newbie) before row 292 (this ref# changes as new info is added to table – of course), but anything below the company Sunbelt (row 292) does not ‘copy’ to it’s worksheet (employees, NEW company) as it should.

          • Hi,

            The array formula (Ctrl+Shift+Enter) in cell A2 of Sunbelt sheet should be:

            =IFERROR(INDEX(‘MASTER SHEET’!$A$11:$Q$371,SMALL(IF(‘MASTER SHEET’!$D$11:$D$371=MID(CELL(“filename”,Z1),FIND(“]”,CELL(“filename”,Z1))+1,256),(ROW(‘MASTER SHEET’!$D$11:$D$371)-ROW(‘MASTER SHEET’!$D$10))),ROW(1:1)),COLUMN()),””)

            Copy this formula down and right. Also, copy this corrected formula to the other worksheets.

  • hi ashish sir

    i was only making sure abt the formula that you wrote in this file, can the same results cannot be extracted with the help of vlookup ?

    Reply

  • Ashish,

    A few months ago you very helpfully created a workbook for me that would split data from one workbook across multiple tabs…. the workbook you created was provided for me at: https://excelenthusiasts.sharepoint.com/Documents/Test%20Membership.xlsx

    However I have now noticed that the entries stop automatically filtering into each of the relevant tabs when I reached the 200th entry (row 203) in the ‘All Members’ workbook.

    Would you be able to amend this so that it will automatically filter up to 500 entries?

    Many Thanks
    David

    Reply

    • Hi,

      You may refer to the correct formula on the “Life” worksheet of this workbook. Just right click on the tab and under Move or Copy > Copy. Rename the new tabs so created with the Categories list in column J of the All Members worksheet.

      Hope this helps.

      Reply

  • Ashish,

    I’ve tried to apply your solution to my workbook, but I have not been successful.

    I have a payroll file that I need to be able to download, change the formatting (column order & calculations) and then separate into individual sheets separated based on the location (country) of the employee.

    “Master File” is the place where I will replace the file each pay period.

    “List” is the file with the calculations running off of the “Master File” and then each other tab is named after the country location and is formatted the same way as “List” but should only contain employees in that country location. I have uploaded a test copy of my spreadsheet with all the calculations I was using and all data contained is test data and not real.

    Please let me know if you have any questions and thanks for your help!

    -Erin

    https://onedrive.live.com/edit.aspx?cid=F5066CC810476E71&resid=F5066CC810476E71%21107&app=Excel

    Reply

  • Ashish,

    https://alabama.box.com/s/1rkahounbultb0qigl0u

    In this workbook, I have several tabs with tasks that need to be completed. There is a column for the status that is conditionally formatted to turn a particular color based on what percentage is complete for that task. I’d like to make each item move to the “COMPLETED” tab once they hit 100.

    Any help you could give would be awesome! I tried to figure it out using some of your other methods, but couldn’t.

    Thanks!

    Aubrey

    Reply

  • By the way Ashish…instead of putting all of the completed tasks on the completed tab, I changed it to “NOT COMPLETED”. That way, we can easily pull up that tab to see all the tasks that have not been completed yet, or do not have 100% next to them.

    Reply

    • Hi,

      Your question is just the reverse of the actual Blog title. You do not want to split data into multiple tabs – instead you want to combine data from multiple tabs into one with the condition that the status column should show a number < 100. It will be far easier to solve the problem if data from all sheets was appended to just one sheet. Once that is done, we can use the formula which I have shared in this Blog article.

      Reply

  • I was just wondering how to add more columns to be sorted on the separate pages. I’ve tried to change it to where it’s sorted by numbers as well and have not been successful. I’ve been playing with your original workbook “split data across sheets” and have not been successful. For example, using your workbook, if I wanted to sort by rating and add a comments column, how would I do that?

    Reply

  • Hi,

    I have about 12 reportees who work on seperate worksheets at seperate workstations. I want to create a mastersheet which would have the same headers like the employee sheets and will get auto populated when they hit a “submit” button after they input the data in their respective sheets.

    Reply

  • There are workbooks A,B,C,D with headers – Name, phn #, e-mail, Location. Each such workbok is assigned to employees, eg Employee A updates workbook A and so on. End of the day someone has to go to each workbook and compile and populate the information onto a seperate sheet. I wanted to know if this can be automated and the information gathered gets auto populated into the mastersheet located on a shared drive.

    Reply

    • Hi,

      If you are using Excel 2010 or a higher version, you can use Power Query (a free add-in from Microsoft) to append data from multiple workbooks (all stored in one folder) to one workbook. So if data is added by rows in any one workbook, then the master workbook would auto update.

      Reply

  • Hi
    I received a response to a question raised on a forum directing me here.
    Thank you for the example workbook. I think (but could be wrong!) that my formula will be slightly different than the example given.
    In the example workbook, it pulls through data based on name, and fills info to the right.
    I would like to pull through data based on clients and fill to the left and right. The client appears in multiple cells and this changes month to month.
    Can you please help?

    Thanks

    Anthony

    Reply

    • Hi,

      You are most welcome. I do not understand your question. Upload your workbook to OneDrive and share the link of the workbook here. Also, please explain your requirement very clearly.

      Reply

      • Hi
        Here is the link: https://onedrive.live.com/redir?resid=3b2e1b57e2df1f1!197&authkey=!AKr8TDt3NOVF0_U&ithint=folder%2cxlsx
        Tab1 = master. This houses all of the raw data. The number of rows increases daily and varies from month to month.
        I will then have proceeding tabs related to the ‘client’ column in the ‘master’ sheet. I have inserted two as an example: Cynefin and Boost.
        I would like the data inserted into ‘master’ to automatically populate in the client tabs if the client’s name is used. This data is to the left and right of the client name in the master tab. This data is sometimes changed, eg to make the notes more specific.

        I believe the above is possible, but please correct me if I am wrong. My next request may not be possible!

        I would then like the data to be sorted automatically in the client tabs.
        Doing this manually is a custom sort:
        1. Code
        2. Date
        3. From
        4. To

        Is there an Excel formula to automatically do this, or will I have to continue this section manually?

        Best wishes

        Anthony

        Reply

        • Hi,

          Please find herein the solution to the first problem. Just create a copy of any of the two tabs and rename it to a new customers. The data in the new worksheet will auto update. I do not have a solution to the first problem.

          Hope this helps.

          Reply

          • Hi
            Thanks for the solution, but I may not have explained the issue correctly.
            I would like only the rows which mention ‘Cynefin’ in the client column (F) cell to populate in the Cynefin tab.
            This would then be repeated ie only rows which mention Boost in the client cell would populate the Boost tab.

            Is this possible?

            Best wishes

            Anthony

            Does this clarify?

          • Hi,

            I have corrected for the mistake. Move or copy any of the tabs and type the Client Name in cell A1 of the newly created worksheet. The data in that new worksheet will update automatically.

            Hope this helps.

          • Thank you.
            Do I need a new Excel document with the correct formula?
            Or do I need to do something in the excel doc you sent earlier?

          • Hi
            The formula doesn’t seem to capture additional data inputted into the master spreadsheet.
            For example, if I include an additional row for Boost, the data is not transferred across to the Boost tab.
            Is there something I can/should change to correct this?
            Best wishes
            Anthony

          • Hi,

            Select the range of data (including the header row) on the source data worksheet and press Ctrl+T. Ensure that the tick box is checked and press Enter. Now when you add data by rows (do not leave any row empty), that row of data will automatically appear in the respective worksheet.

            Hope this helps.

          • Hi
            I have inserted =sum formulas in B3 of each client’s tab but some of them do not work correctly.
            I want the sum to total the time spent on each client, which is column E.
            Some of the tabs work fine, but some do not. It appears that the ones that do not work fine have a larger time amount than the others.
            The tabs that do not work are Jones, WAA, APNB and Admin.
            Can you please assist?
            Best wishes

            Anthony

            https://onedrive.live.com/redir?resid=3B2E1B57E2DF1F1!200&authkey=!AGIYfwHrAthgCFw&ithint=file%2cxlsx

          • Hi,

            I am unable to download your workbook but if it is the same one which I had shared with you (split-data-across-sheets v4), then i get the correct answer when i create worksheets for WAA and Jones. For WAA, the total for column E is 2:50 and for Jones it is 4:05

          • Hi
            Here is the workbook. You should be able to open this one.
            All data is copy and pasted into the master tab.
            The data in the master tab is then automatically transferred, thanks to the formulas, to the relevant client tab.
            As detailed previously, I have inserted =sum formulas in B3 of each client’s tab but some of them do not work correctly.
            I want the sum to total the time spent on each client, which is column E.
            Some of the tabs work fine, but some do not. It appears that the ones that do not work fine have a larger time amount than the others.
            The tabs that do not work are Jones, APNB and Admin.
            Can you please assist?
            https://onedrive.live.com/redir?resid=3B2E1B57E2DF1F1!204&authkey=!AFbEeqXTyAfU45g&ithint=file%2cxlsx

            Thanks
            Anthony

          • Hi,

            The formula in range B3:B4 of all sheets are correct. Try this for the Jones worksheet first and then replicate for the other sheets as well

            1. Select B3 and press Ctrl+1
            2. Change the Custom format to [h]:mm
            3. Select B4 and press Ctrl+1
            4. Change the Custom format to [h]:mm;[Red][h]:mm

            Hope this helps.

          • Thanks – that works perfectly.
            I’m very grateful for all your help.

            Kind regards

            Anthony

  • Hi Ashish,

    I have read all the posting here and you are seriously great with this. salute that!
    Anyway, I need your favor on how I can separate data from sheet ‘Master’ that:

    IF (BR6@Order Status = ‘Completed’), the whole row will move to new sheet called ‘Completed’ via macro, leaving status pending in ‘Master’ sheet.

    Data in sheet ‘Master’ will keep increasing manually(i was thinking to use VLOOKUP later, to eliminate manual update), and when user click button Run Macro, it will copy the new ‘Completed’ data to new row below to previous one.

    File has been uploaded to the following link.

    http://1drv.ms/1bA6G1I

    Looking forward to your feedback.

    Thanks.

    Reply

      • Hi Aishish,

        I prefer to have macro solution as the excel file that I share to u, somehow later will have a direct query to database.

        Thanks bro.

        Reply

        • Hi,

          Sorry the delay in replying but am travelling and may not be able to take time out to frame the VBA code to solve the problem. If I get some free time, I will post my solution here.

          Reply

          • Hi,

            It’s ok.. I will regularly check my email for the updates from you..

            Thanks,
            Hisyam

          • Hi,
            sorry for disturbing..
            is it possible for me to get a solution with macro and without macro?

          • Hi,

            In cell A6, enter this array formula (Ctrl+Shift+Enter) and copy down/right

            =IFERROR(INDEX(Master!$A$6:$BY$81,SMALL(IF(Master!$BR$6:$BR$81=”Complete”,ROW(Master!$BR$6:$BR$81)-ROW(Master!$BR$5)),ROW(1:1)),MATCH(Completed!A$5,Master!$A$5:$BY$5,0)),””)

            Hope this helps.

  • Hi Ashish,

    Thank you very much for posting this – it’s very useful in my project.

    Quick question – How can I edit the formula so that if a column value is empty, it will return the empty string “” instead of 0?

    Thanks!

    Reply

    • You are welcome. Edit the array formula in that column to

      =IF(formula=0,””,formula)

      Remember to confirm the formula with Ctrl+Shift+Enter.

      Hope this helps.

      Reply

    • Yes. Since this column’s formula will be different from the formula in the other columns, you will get an information triangle telling you that the “Formula is inconsistent”.

      Reply

  • I know this topic has been dealt with many times, but the answers are a bit over my head in that I can’t seem to follow how to adapt code to my particular situation.

    I have a workbook with 2 sheets.

    Sheet 1 contains data in columns A through I, rows 15 – 56.

    I am looking for VBA code, or, better yet, a formula I can enter on sheet 2 to do the following:

    1. Copy and paste all rows (columns B – I) within rows 15-56 where the entry in column B is “Lead” together on sheet 2 as a group ;

    2. Copy and paste all rows (columns B – I) within rows 15-56 where the entry in column B is “Co” together on sheet 2 as a second group.

    Thanks for the help. I would appreciate having any variable in the code defined for me so I can later adapt to any changes in worksheet layout.

    Reply

      • I have uploaded a sample workbook here: https://onedrive.live.com/redir?resid=671BAC74FD14F861!107&authkey=!ACoeRHZo6SgNrF4&ithint=file%2cxlsx

        The solution I am looking for would copy two groupings of rows from Sheet 1 onto sheet 2. One grouping would be all of the rows from sheet 1 with the entry “Lead” in column B. The second grouping would be all the columns from sheet 1 with the entry “CO” in column B.

        I also want column headings from sheet 1 (Row 14, column B – I) to appear on sheet 2, but I would assume that these can just be copied into the sheet.

        It is important that as data is entered or changed on sheet 1 that the summary on sheet 2 automatically updates. Another alternative might be a “button” on sheet two that the user can click when the sheet 1 data is final.

        I hope this is what you are looking for and I really appreciate the help.

        Reply

        • Hi,

          What do blanks in column B signify? Should B17:B18 considered as Lead, B22 as Co and so on? If yes, then please upload the revised workbook with all blanks cells of column B duly filled up.

          Reply

          • WOW! This is perfect. I will play with this more tomorrow, but it is exactly what I was trying to do only better in that the separate sheets for Lead and Co are better.

            If I rename “Sheet1”, can I do a global replace in the formulas to affect the name change.

            Also, I would like to explain the use of this form to you. Can you send me an email with an address where I can send a private message to you.

          • You are welcome. Renaming any sheet will not effect the formulas at all – so rename any sheet you like. Please participate her itself (rather than sending private messages over mail) for mutual learning.

  • Similar to this query: I have a workbook with sheets for each employee, tracking quarterly hours and locations worked. I would like to take the “total tally row” from each employee & have it automatically appear on another sheet so the company totals can be tallied from there. How would I accomplish this?

    Reply

    • Hi,

      What you want to accomplish is the exact opposite of what I have described in my Blog post. My Blog post is about creating multiple worksheets from one worksheet. Yours is about creating one worksheet from multiple worksheets. Please refer to my solution at this link.

      Hope this helps.

      Reply

  • This is exactly what im looking for my workbook, but unsure how to implement it. I have attached a link to it here. Basically, i need all the data on the input sheet, dynamically linked to named sheets based on the names in column I. If data is changed or added on the input sheet, then the named sheets need to update too. Hope you can help. Thanks
    https://onedrive.live.com/redir?resid=6D71015338011158!125&authkey=!ACpLryRic_QUmVg&ithint=file%2cxls

    Reply

      • Wow! That is excellent, many thanks. Is there a possiblity that the names in Column J can be copied to the respective named worksheets? Also is there a way that when named worksheets are updated, the Blanks in Column J are blank & not show a zero?
        I appreciate your assistance with this!

        Reply

      • Thanks, that worked. As per the first part of my question, where there are names in both column I and J, is there a way to add that row to both named sheets? e.g if there is “Paul” in column I & “Anna” in column J, can that row be added to the Paul AND Anna named worksheets?

        Once again, many thanks for your help

        Reply

  • Hi Asish,
    Once again, many thanks for your assistance. I’ve been playing with the formula you have created to attempt some modification myself, but to tell the truth, it goes right over my head!
    I have one more thing to ask. Could you possibly implement your formula on the revised workbook in the attached link?

    https://onedrive.live.com/redir?resid=6D71015338011158!129&authkey=!AFkLCNnCtptWpNM&ithint=file%2cxlsx

    I really appreciate all your help with this project, & if you have a paypal account, i would like to make a donation? I cant expect you to do all this for me for free, as i’m sure you’re a very busy person.

    Many thanks

    Reply

  • Hi.

    I am hoping you can help with my query.

    I have a workbook I have created to keep track of complaint cases I raise. It covers 7 different columns of information I need to save. When I mark one case for ‘follow up’ in one column on the first sheet, I’m looking for that whole row to be copied onto a second sheet. So that I have all my cases for follow up on this second sheet without having to sift through my other ‘open’ or ‘closed’ cases.

    I’m pretty clueless when it comes to excel pretty much so any help would be very appreciated.

    Thanks,
    Tim

    Reply

  • Hi Ashish,

    I was hoping you can help with this spreadsheet. I’m hoping to move projects into two different sheets based on their Project Priority (L). I can’t figure out how to manipulate the formula you are using to make that happen. Ideally I’m hoping to have one sheet for 5 and above projects and one sheet for 5 and below. I have uploaded the excel file to google drive. I would greatly appreciate any help!!

    https://drive.google.com/file/d/0Bw2B2cjSE7A_eDg5V2R1YmpoTFk/view?usp=sharing

    Thanks!
    Grace

    Reply

      • Thank you so much! This is great. I’m curious – do you have further explanations anywhere about how you create these formulas? I was trying to figure out how to modify the sheet if I wanted to change the value from 5 to 4,6 etc. Or create another sheet that had only =5. Etc. Any suggestions for how to modify.

        Thanks for your help! I really appreciate the time you’re spending to help this is an awesome resource!

        Reply

        • If you ignore the last comment- I figured that out, and I have a different question.

          Is it possible to add a second criteria? I.E. sort by sheet based on the potential number (column L), but if “ACTIVE” (column C) – don’t add to the new sheets and if “JSQB” (column C) add to a new JSQB sheet?

          Thanks!!

          Reply

          • Hi,

            I am glad you could solve your problem. You may refer to my solution in this workbook.

            The second worksheet will show all rows where the Priority <=5 and the Priority is not Active. The third worksheet will show all rows where the Priority >5 and the Priority is not Active.

            Hope this helps.

  • Hi Ashish,
    I have been using the spreadsheet that you kindly modified with your formula without any problems.
    My Master sheet contains monthly input data, & if i need to add data mid table i click on the row, insert row & add the data. The worksheets then update as normal. My problem is that at month end, i need to clear the data input on the master sheet ready for the next month. When i input data after clearing cell contents, the sheets update as they should. But if i need to now add data mid table rather then at the end, the worksheets go blank. Is there a resolution to this problem?

    I have attached my file for you to see.

    Many Thanks!

    https://onedrive.live.com/redir?resid=6D71015338011158!133&authkey=!AFXeJDphitP08G8&ithint=file%2cxlsm

    Reply

    • Hi,

      Do not add data in the middle of your Table. Add data at the end instead. To allow the formula range to automatically extend to the last row till where you data is, you must first convert the source data range into a Table. Select the source data range (including the header row) and convert it into a Table.

      Now add rows of data from the first available blank row. The ranges specified in your formula will auto expand.

      Hope this helps.

      Reply

  • Hi Ashish,
    Thanks for your reply.
    I input the monthly data before the month starts, so January data is added in December. Throughout January, new jobs may come in, that’s why i add data mid table to keep in date order. As stated on my original workbook, i could add data mid table & the worksheets would update as they should. Only when i clear data for the whole table will it not then allow data to be added mid table, if i do the worksheets go blank. I was just curious why initially it updates data added mid table, but after clearing cell contents it won’t.

    Reply

    • Hi,

      You may refer to my solution in this workbook.

      1. Right click on the People Operations tab and select Move or Copy > Create a Copy
      2. Rename the newly created worksheet to any existing department, say Growth

      Repeat the steps above for extracting data for other departments.

      Hope this helps.

      Reply

      • Hi Ashish!

        Thanks for the prompt response.

        I was able to replicate the remaining Department sheets for Growth, Digital Product, Customer Support

        I was unable to replicate the sheets for the following Depts:
        1) Technology
        2) Experience
        3) Product
        4) Communication Design

        The above four departments show up as empty cells… do you know what might be the problem?

        Reply

  • This helps quite a lot! However I am having trouble adding new data to the master sheet, it does not automatically update new entries as it should. I’m not sure what the problem is exactly but I noted something buggy with the final row in the “AGENCY FORCE” tab. Whenever column H says “New” in the bottom row, my “New” tab does not get populated with data as it should.

    Reply

    • Hi,

      Just select the entire dataset on the Agency Force worksheet (including the header row) and press Ctrl+T. Now when you add more rows of data on this worksheet, the other sheets should automatically pick up the new rows of data.

      Hope this helps.

      Reply

  • I have a similar situation as William back in 2014 on another one of your threads (programmatically transfer …). I posted it here it on that page as well as here since the responses have been much more recent.

    I have a bunch of client data that will be copied from another source and pasted into the “Master” sheet. From the Master sheet I would like the data to be split into different sheets based on two criteria: Client (Column C) and Term Length (Column AF). The pasted data will be columns A through AB and varying row numbers. I put column AC in for the copier to know not to mess with columns AD through AF. These are calculated after the data has been pasted. Term Length (column AF) is used to determine if the Expected date (column J) is within 30 days (short), between 30 and 90 days (mid), and over 90 days (long).

    Ideally the macro would create three different sheets (North, West, and South clients). Currently I don’t have a way to do that but was thinking about adding column like the Term Length to sort that way. If you have a better suggestion please let me know.

    The first sheet (North) would be sorted as follows (typical for all sheets):
    – Is it a North client? If yes, then include on this spreadsheet
    – The first section (short term) would be sorted as follows (typical for all sections):
    – Does Term Length (column AF) = 1 If yes, then include in this section
    – Start with first alphabetical client
    – include all proposals for that client that are also Short Term (Term length = 1)
    – include a total row at the bottom of this section
    – start with the second alphabetical client
    – repeat until all clients classified as North are completed
    -include a grand total row that sums all the totals from each client section
    Complete the same steps for the other sheets (south and west)

    https://onedrive.live.com/redir?resid=FF3D0840CD7995ED!114&authkey=!AKS3U9vTEFjUN90&ithint=file%2cxlsx

    Please let me know if you have any questions. Thank you so much in advance!

    Reply

    • Hi,

      There is only one criteria you have for transferring data to multiple worksheets i.e. Client name appearing in column C. Thereafter on every client worksheet, you want to sort the data in a certain order and show subtotals. My formula based solution will only accomplish the task of creating one worksheet per client – not of sorting rows in a certain order.

      This would require a VBA based solution.

      Reply

  • Right that’s what I figured I was looking at your VBA solution in the original excel file you had attached to the programmatically transfer data thread. The problem I was having is understanding how to adapt your example into a much larger table and to incorporate the specific requirements stated above.

    Reply

  • Hi Ashish,
    I am not very good in programming in Excel, if you can guide me that will be very helpful.Will really apprecaite it very much

    I have a master sheet with approximately 345 rows of data for workers, and I want to make a separate sheet for each worker. so that I can print each workers sheet sperarately.

    The headers in the master are A:L (Name, Type, Deposit, Refund, hire date, term date, SSN, total, years worked, Rate, refund method)

    I want the data in each sheet to be displayed in two colums A and B
    A B
    Name John Smith
    SSN 000000000
    years worked 1
    Type new
    Deposit 100
    Refund 50
    Total paid 2000
    Rate 1000
    amount owed = Deposit – Refund (100-50=50)
    Overpaid = Total paid-Rate-Amount Owed(2000-1000-50 = 950)

    Reply

  • Hello, I have been trying all day to get the move -> copy and filter bsed by TAB name to work with out success. Can you help please?

    Reply

    • I am trying to create a new sheet for each Group

      I used this formula, I couldn’t get it to work. Circular Reference error

      =IF(ISERROR(INDEX(Master!$A$1:$C$7072,SMALL(IF(Master!$B$1:$B$7072=MID(CELL(“filename”,O1),FIND(“]”,CELL(“filename”,O1))+1,256),ROW(INDIRECT(“$1:$”&COUNTA(Master!$B$1:$B$7072)))),ROW(1:1)),MATCH(A$1,Master!$A$1:$C$1,0))),””,INDEX(Master!$A$1:$C$7072,SMALL(IF(Master!$B$1:$B$7072=MID(CELL(“filename”,O1),FIND(“]”,CELL(“filename”,O1))+1,256),ROW(INDIRECT(“$1:$”&COUNTA(Master!$B$1:$B$7072)))),ROW(1:1)),MATCH(A$1,Master!$A$1:$C$1,0)))

      Reply

      • I got your formula to work. Awesome piece of work!!!! Going to save me a ton of time in what once used to be a very manual process.

        I do have a question though. If I were to delete rows from my Master Sheet/Table that completely removed an entire group could it also delete the sheet that corresponded with it? I know it removes the records from the sheet, but it could it delete the sheet too?

        Reply

  • Hi Ashish,

    Great results you’ve achieved in helping so many people! Hoping you’ll be able to assist me as well.

    I have two tabs: InProgress and Completed, each worksheet contains the same headers.

    While in InProgress, if column E “Status” is changed to “Confirmed”, I’d like the entire row to move from InProgress sheet to Completed sheet.

    Could you please help?

    http://bit.ly/29qH7SI

    Thank you!!

    Reply

  • Hi Ashish,

    Thanks for the above workbook. It really does help a lot. Just a quick question,
    rather copying the data to multiple sheets based on the sheet name, can we reference in to a specific cell with the “Department” name?

    I have tried replacing the below with a simple cell reference, but it does not
    seem to work:
    MID(CELL(“filename”,P3),FIND(“]”,CELL(“filename”,P3))+1,256)

    Thanks in advance for your advice.

    Reply

    • Hi,

      I will be able to help you if I understand your question properly and see your workbook. Upload your workbook to OneDrive and share the download link here. Please also explain the question clearly.

      Reply

  • Hi Ashish

    Thanks for the offer of help on the MS Forum.

    I am trying to input everything into one file then have:
    1. One sheet per department (one tab) for each
    2. One sheet with a dropdown for systems where all data per system will be shown (regarldess of area and dept)
    3. One sheet with a dropdown for areas where all data for an area where will shown (regardless of system and dept)

    Hope it makes sense, the link is here: https://1drv.ms/x/s!AqR4na_FAsEJnDBlkugRnFrUY2LJ

    Shall much appreciate your help.

    Happy new year!

    Reply

    • Hi,

      1. Enter this array formula (Ctrl+Shift+Enter) in cell A6 of the OS Work Dept 1 worksheet and copy down/right

      =IFERROR(INDEX(‘OS Work Input’!$A$3:$K$6,SMALL(IF(‘OS Work Input’!$H$3:$H$6=$G$3,IF(‘OS Work Input’!$J$3:$J$6=”Outstanding”,ROW(INDIRECT(“$1:$”&COUNTA(‘OS Work Input’!$H$3:$H$6))))),ROW(1:1)),MATCH(A$5,’OS Work Input’!$A$2:$K$2,0)),””)

      2. Right click on the tab and select Move or Copy > Create a Copy
      3. Rename the tab to whatever you wish
      4. Change the value in the drop down of cell G3 to Department or Area of choice.
      5. Repeat steps 2 to 4

      Hope this helps.

      Reply

      • Hi Ashish

        For some reason it doesn’t seem to work, when doing this on OS Work Dept 1 I get only the job that is for Dept 3 showing.
        Also it still shows if the status us marked as completed.

        Been trying to follow the formula through, it seems not to be checking G3 (Dept Name) properly.

        Appreciate the help!

        Reply

        • Hi,

          It worked absolutely fine for me. The formula has to be confirmed with Ctrl+Shift+Enter (not just a simple Enter). If that still does not work, then upload the workbook (to some file hosting service and share the download link with me) in which you have copied and pasted my formula – I will correct for the error.

          Reply

          • Hi,

            On the System worksheet, the array formula (Ctrl+Shift+Enter) in cell A6 has to be:

            =IFERROR(INDEX(‘OS Work Input’!$A$3:$K$6,SMALL(IF(‘OS Work Input’!$E$3:$E$6=$G$3,IF(‘OS Work Input’!$J$3:$J$6=”Outstanding”,ROW(INDIRECT(“$1:$”&COUNTA(‘OS Work Input’!$H$3:$H$6))))),ROW(1:1)),MATCH(A$5,’OS Work Input’!$A$2:$K$2,0)),””)

            On the Area worksheet, the array formula (Ctrl+Shift+Enter) in cell A6 has to be:

            =IFERROR(INDEX(‘OS Work Input’!$A$3:$K$6,SMALL(IF(‘OS Work Input’!$C$3:$C$6=$G$3,IF(‘OS Work Input’!$J$3:$J$6=”Outstanding”,ROW(INDIRECT(“$1:$”&COUNTA(‘OS Work Input’!$H$3:$H$6))))),ROW(1:1)),MATCH(A$5,’OS Work Input’!$A$2:$K$2,0)),””)

            Hope this helps.

      • Dear Ashish,

        Good Morning

        please check below link
        https://www.dropbox.com/s/li99ewr4xer2vv2/Master%20List.xlsx?dl=0

        this is my requirement:
        Masterlist I wants to input all the detail from column A to S then I will choose that data for
        1. SO or
        2. MP or
        3. SO & MP

        Example If I choose SO in the status column it should be show entire row in SO Sheet.

        Finally I want to edit Master list and have to get two reports
        1. For SO
        2. For MP

        thanks
        Kaj

        Reply

        • Hi,

          In cell A14 of the SO worksheet, enter this array formula (Ctrl+Shift+Enter) and copy down/right

          =IFERROR(INDEX(‘Master List’!$A$14:$U$16,SMALL(IF(‘Master List’!$T$14:$T$16=”SO”,ROW(‘Master List’!$A$14:$A$16)-ROW(‘Master List’!$A$13)),ROW(A1)),COLUMN()),””)

          In cell A14 of the MP worksheet, enter this array formula (Ctrl+Shift+Enter) and copy down/right

          =IFERROR(INDEX(‘Master List’!$A$14:$U$16,SMALL(IF(‘Master List’!$T$14:$T$16=”MP”,ROW(‘Master List’!$A$14:$A$16)-ROW(‘Master List’!$A$13)),ROW(A1)),COLUMN()),””)

          Hope this helps.

          Reply

  • Dear Ashish,

    Good Morning

    please check below link
    https://www.dropbox.com/s/li99ewr4xer2vv2/Master%20List.xlsx?dl=0

    this is my requirement:
    Masterlist I wants to input all the detail from column A to S then I will choose that data for
    1. SO or
    2. MP or
    3. SO & MP

    Example If I choose SO in the status column it should be show entire row in SO Sheet.

    Finally I want to edit Master list and have to get two reports
    1. For SO
    2. For MP

    thanks
    Kaj

    Reply

  • Hi, are you able to tell me what formula to use in this case:
    My workbook has one tab for a Running Total and several different tabs for separate years. Whatever is added to year 2017, 2018 and so on, should automatically appear on the Running Total tab. There are several columns with totals and averages on the bottom. Thank you

    Reply

  • Hi Ashish – Happy New Year 2018.
    I have a doubt on moving data from 1 sheet to another based on a criteria.
    I was able to pull all the data from WEB and have it in my Excel. The data is refreshed every 1minute. Now, based on a criteria(NSE or BSE) I want to pull selective rows only from this sheet to another.

    Please note that the excel sheet contains values other than NSE and BSE too.

    Reply

  • Good afternoon Sir,

    I’m currently stuck with some problems, the first is grouping datas together, ie A,B,C,K =”Europe” and the rest = U.S”. The next is the splitting the data into multiple tabs(Europe and U.S) once I have grouped the datas together. Can you please help me with my problem? I’d be more than happy to make a donation if you can help me with my problem along with some explanation!

    https://www.dropbox.com/s/r22ie76op3h5ofl/Testing.xlsm?dl=0

    Reply

  • Hello Ashish,

    I have read every use case above and can’t quite find a solution that matches my need. You seem to be extremely knowledgeable in this area and I greatly appreciate your help. You have multiple 1:M examples of pulling data from a single “master” sheet and populating data on multiple other sheets based on a criteria. I need to do the opposite. I need to do M:1 where I am pulling rows of data from detail sheets and summarizing select rows of data from each detail sheet into a summary sheet. The summary sheet will have a “section” for each rolled-up data set. For example, I have three detail sheets, “Tasks”, “Issues”, “Risks”. Each of these detail sheets has a column named “Status Report” that has a Yes or No value. I have a fourth sheet that is named “Status Report”, which is my summary report. I would like to have three sections on this report that pull all Tasks, Issues & Risks from the detail sheets where the Status Report field=”Yes”. I’ve attached a sanitized example for your review/comment. Thank you so much!

    https://drive.google.com/file/d/1yvhYiBggzzidE0Au7lad_XTk3ONZrjp6/view?usp=sharing

    Reply

    • Hi,

      You may find my solution in A9:G12 of the Summary worksheet in this workbook. Ensure that the spelling in the A8:G8 are the same as those in A1:H1 of the Tasks worksheet. Please note that those are array formulas and have to be confirmed with Ctrl+Shift+Enter. Replicate this for the other 2 sections as well.

      Hope this helps.

      Reply

  • Hello Ashish,
    I have been looking everywhere trying to figure out how to make this work. After reading all of these posts it looks like you might be able to help me and i would really appreciate it. Attached below is a spreadsheet i’m working on.

    What i would like to happen is once new Data is input on the jobs tab i would like for certain data to be transferred to the other sheets. I want to set it up based off of salesperson. So if Ryan is entered as the salesman, the job name, amount, and payment source would be transferred to his sheet. The other salespeople and so on. Does that make sense? Is this possible? Please let me know what i can do. thank you!

    https://drive.google.com/file/d/1IwX3a1ZiYRp5mmymwDiDlVbceN3-kQVS/view?usp=sharing

    Reply

    • Hi,

      In cell A2 of the Ryan sheet, enter this array formula (Ctrl+Shift+Enter) and copy down/across

      =IFERROR(INDEX(Jobs!$A$6:$K$17,SMALL(IF(Jobs!$G$10:$G$17=MID(CELL(“filename”,O1),FIND(“]”,CELL(“filename”,O1))+1,256),ROW(INDIRECT(“$1:$”&COUNTA(Jobs!$G$6:$G$17)))),ROW(1:1)),MATCH(Ryan!A$1,Jobs!$A$5:$K$5,0)),””)

      Right click on the tab and select Move or Copy > Create a Copy and just rename the tab to a salesman’s name. The data in that tab should auto update.

      Hope this helps.

      Reply

  • Ashish,

    I’ve been pulling my hair out trying to make this formula work for my spreadsheet and am obviously doing something wrong. Can you assist?

    Reply

  • Hello,

    Which part of the code do I edit to specify a sheet (i.e. I don’t want to base it from the sheet name?

    Regards,
    LeBron

    Reply

  • Hello,

    This thread has been amazing! I have been trying to utilise this info for my own spreadsheet but am having a couple of issues

    I was wondering if it would be possible to have tabs set up to separate the merchandise by brand?
    The brand name isn’t in a separate column but is instead included in the ‘item description’ column.

    Would it also be possible to add together the duplicate quantities in the ‘on hand’ column even if the item is from a different ‘warehouse’

    Please see the link for an example of the type of info I’m using

    I’m hoping to not have to alter the layout on the ‘master’ tab as this is a report dump from the system and this is just a very small sample

    Sorry, I know I might be asking a lot but I have spent ages trying to figure this out

    John

    https://drive.google.com/file/d/1VJl-Vu8qa0vMNhWgo69E16GhfV0j9UI-/view?usp=sharing

    Reply

    • Hi,

      I am confused about your requirement. If your objective is to “add together the duplicate quantities in the ‘on hand’ column”, then you must build a Pivot Table. A Pivot Table will be built only when all your data is on a single worksheet. If your objective is to split data into multiple worksheets, then this Blog post is relevant to you.

      So what exactly do you want?

      Reply

  • Thank you for replying 🙂

    In previous solutions you have provided an array formula that splits data in to separate tabs, formulating this to do so based on the name of the tab
    This is really great but I was hoping to ask if this would be editable.

    My query can be in two parts

    1/. Is it possible to edit this array formula to split the data in to tabs based on a partial description within a longer description? I.e. The brand – “Penguin”. I know in some other formulas quote symbols (“) are used in this way but I can’t see how this fits in to the array formula

    2/. Would it be possible to find all the quantities “on hand” of a unique “item number” even if it is in a differing “warehouse”

    I realise part 2 might be a little difficult to incorporate with but if you can point me in the right direction with at least part 1 that would be great

    Thank you again so much

    Reply

    • Hi,

      I can help with Part 1. Share the link from where I can download your Excel file. Show the expected output there as well. Also, on what basis should the data be split into multiple tabs?

      Reply

    • Hi,

      In cell A3, enter this array formula (Ctrl+Shift+Enter) and copy down/right

      =IFERROR(INDEX(Master!$A$3:$AW$119,SMALL(IF(ISNUMBER(SEARCH(MID(CELL(“filename”,O1),FIND(“]”,CELL(“filename”,O1))+1,256),Master!$D$3:$D$119)),ROW(Master!$A$3:$AW$119)-ROW(Master!$A$2)),ROW(1:1)),MATCH(OVERSPEX!A$2,Master!$A$2:$AW$2,0)),””)

      Now right click on the tab and select Move or Copy > Create a Copy. Rename the new worksheet to Bolle. Repeat this process.

      Hope this helps.

      Reply

  • hi, this thread helps me a lot as I was reading it, now, I’m having my own difficulties and hoping that you could help me with my database. I want to separate/copy the “mortality” and “morbidity” also the “not mortality and not morbidity” in different tabs. But not totally deleted to the main database. I hope you could me as soon as possible. Thank you and I’ll be waiting.

    Reply

  • Hi,

    Thank you very much. I want the Morbidity and mortality cases type will be copied to another sheet, like the example I made to the other sheet.

    Thank you very much for your help.

    Reply

    • So, one has to look into the Case Type column and if the word Morbidity or Mortality is found in that column, then copy those rows to another worksheet. Am I correct? It will be much easier to solve the problem if you can live with the solution being in 2 seperate worksheets – one showing all cases of Morbidity and another showing all cases of Mortality.

      Also, if your MS Excel version supports Power Query or Data > Get & Transform, then we do not even need to write a formula. We can directly get all rows of data where the words morbidity or mortality are found in one sheets itself (the result will be dynamic).

      Let me know.

      Reply

    • For Excel 2013, download and install Power Query from the Microsoft website. Once installed, go to Power Query > Queries and Connections and pane will open up on the right. Right click on the Query and then in the Query Editor, review all the steps in the Applied Steps section.

      Reply

  • Hi, can anyone help me to use for ex: cell B4 (sheet Master) to A4 (sheet HR), if the cell B4 is text?

    When I’m using:

    =’Master’!B4 it’s showing me the function name ‘=’Master’!B4′ but I need the name, so I can use also other names calling exacty the name.

    *is it any possibility to use one function that will “clone” the cell?

    Thanks in advance!!

    Reply

  • Hello, I am trying to create a spreadsheet for a cricket club. The spreadsheet will highlight the ages of the members on a given date so we know the age specific date. I want each age to be copied to a new sheet so we can easily check the players for each age group. Can you help?

    Reply

    • Hi,

      Try to apply the formula shared in the original blog post. If you are unable to, then share the link from where I can download the file. In the file, very clearly show your expected result.

      Reply

  • Hi,

    This formula (MID(CELL(“filename”,O1),FIND(“]”,CELL(“filename”,O1))+1,256)) use filename(sheet name) as the criteria to copy the data right?

    What if the sheet name doesn’t represent the criteria? Thank you.

    Reply

  • I will use your workbook as reference.
    For example, There are two master list for base in Dubai and London. So i have to split the data into sheets named Account_London, HR_London, Account_Dubai and HR_Dubai.

    Reply

    • Hi,
      One will have to create a single master list first. Then we can split into 4 worksheets based on the occurrence of the word Account and HR in some specific column.

      Reply

  • Hi Ashish,

    Thanks for suggesting your page.
    I tried to open your workbook at the top but it won’t let me?

    The person on excel forum that helped me solved my initial question. However maybe you can help with my next question please?
    I would like data from one sheet to be transferred to a new sheet if data in one column is either “word1” or “word2” or “word3”?
    Is that possible?
    If so how do you build the formula for it to work?

    Many thanks

    Rebecca

    Reply

  • Hello Ashish,

    I am trying to split the data from master sheet to multiple sheet. The multiple sheet are getting created but the data is not arrange in correct way.

    Please find the code below.

    Sub SplitandFilterSheet()
    ‘Step 1 – Name your ranges and Copy sheet
    ‘Step 2 – Filter by Department and delete rows not applicable
    ‘Step 3 – Loop until the end of the list

    Set wkb = ActiveWorkbook
    Set wks = ActiveSheet
    Set sel = Selection

    Dim Splitcode As Range
    Sheets(“Master”).Select
    Set Splitcode = Range(“Splitcode”)

    For Each cell In Splitcode
    Sheets(“Master”).Copy After:=Worksheets(Sheets.Count)
    ActiveSheet.Name = Sheets.Count

    With ActiveWorkbook.Sheets(cell.Value).Range(“MasterData”)
    .AutoFilter Field:=8, Criteria1:=”NOT EQUAL TO” & cell.Value, Operator:=xlFilterValues
    .SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End With

    ActiveSheet.AutoFilter.ShowAllData
    Next cell
    End Sub

    Reply

  • Hello,

    I don’t know if this thread is still active, i really hope it is. It helped a lot so far. But to go forward in my project i need a little bit of help. (I’m a novice)

    So your formula copy rows in sheets having the same text as in the “referenced” column.

    A little bit of context : I used your formula to create boards (within sheets) for employees in which we can find “reviews” of their performance on different jobs.
    Using the column A (of Master sheet) as reference to distribute each rows in the right sheet.
    But now i want to create a second board (in the same sheet) using a different column as reference, in this case, column E (of master sheet).
    Because i want 1 board for normal employee listing which instructor trained them.
    And i want 2 boards for instructor, for the first board same thing as the normal employee (because instructor also get reviews by higher up), but also a second board listing which employee they trained.
    I don’t know if that makes sense x)

    I tried modifying your formula by replacing Master!$A$1:$A$16 by Master!$E$1:$E$16
    but i’m having no luck so far. Always getting the wrong data or no data at all.
    I don’t know if it could work ?

    Here’s my file !

    https://drive.google.com/open?id=1kGD0lQVL3zBjaQ9LbqkYFJ4QVxJUSyRl

    In any case, thank you for your work, i learned a lot !

    Reply

  • Ok, so basically, if i change whatsoever in the formula it won’t work… I’m lost, is there some black magic behind it ? In my file formulas won’t update when my master file expand… it used to.
    Do i have to it specific keys when i enter the formula ?

    (My older comments are just above this one, Ashish Mathur, you said “you are welcome”, but my problem isn’t fixed haha)

    Reply

  • Sorry for delay in reply! The problem was in range selection of your formula. I’ve highlighted those ranges here

    https://imgur.com/a/3YJUCdt

    Your data should update accordingly, Since your master list is converted to Table. Although the below one should work where reference is liked to Table instead hard coded range as previous.

    https://drive.google.com/file/d/1PkFScPpmASSjr_KZbrfwZ4GtqCEMKYeP/view

    Hope this helps! Nice to see Ashish is back.

    Reply

  • Hello,
    Looking for some assistance on a sheet and this page seems to provide the closest answers I have found.

    I have a Worksheet (Master) with personal information (i.e. Rank, L Name, F Name, MI, Section) in columns, A,B,C,D,E. I need to copy row A5:E5 based on values being true in cells g5 and q5 to a new worksheet (Team). I need it to do this for all rows in the sheet while referring to the corresponding reference cells in the same row as the personal data.

    I can get it to work with only referencing one cell (i.e. g5) but not both reference cells.

    Any help is much appreciated. Also, I am working on extremely limited connectivity where downloads are not feasible, is there a way to post the formula on the response?

    Reply

  • I am having a sheet with a coloum “Area”. I want to split master data into different “Area” sheets, this is exactly as you have shown in your example of master and department.

    Kindly help

    Reply

  • Hi, Ashish. I’m trying to copy data from a master sheet that contains certain info to another sheet. I also want it to so that if I remove information from the master, the row will be deleted on the linked chart. Can you assist with this please?

    Reply

    • Hi,
      Paste the headings from range A1:I1 of the Dakhl worksheet to the cell A1 of Office expenses worksheet. Furthermore, you may shorten the formula in cell A2 of the Office expense worksheet to:
      =IFERROR(INDEX(Dakhl!$A$1:$H$15,SMALL(IF(Dakhl!$I$1:$I$15=MID(CELL(“filename”,O1),FIND(“]”,CELL(“filename”,O1))+1,256),ROW(INDIRECT(“$1:$”&COUNTA(Dakhl!$I$1:$I$15)))),ROW(1:1)),MATCH(A$1,Dakhl!$A$1:$H$1,0)),””)
      This is an array formula so please confirm with Ctrl+Shift+Enter
      Hope this helps.

      Reply

  • thx a lot, it worked, how can I change the formula to collect data from “Dakhl” sheet cell B2 to H2, and display it in “Office Expenses” sheet cell B2 to H2?
    thanks, I really appreciate your response.

    Reply

  • All of your is amazing @Ashish! I’m struggling with a much more basic version of this. I simply want a row of information to be created in another sheet if any of a number of cells in another sheet (on a singular row) are not blank.

    I collect training feedback and offer the users to submit comments so if they do put a comment in I want that comment to populate a row in another sheet to allow us to review and action that. I hope that makes sense

    Reply

  • Is there any way I can get the links – hyperlinked in the master sheet work with this formula?

    I am able to follow through and it works perfectly fine except for the links.. I have to go back to the master sheet to open the links.

    Reply

      • It does if you just PASTE the links in a cell in master (not HYPERLINK) and use HPERLINK before second INDEX.

        FORMULA:
        {=IF(ISERROR(INDEX(Master!$A$71:$K$272,SMALL(IF(Master!$D$71:$D$272=MID(CELL(“filename”,M2),FIND(“]”,CELL(“filename”,M2))+1,256),ROW(INDIRECT(“$1:$”&COUNTA(Master!$D$71:$D$272)))),ROW(2:2)),MATCH(A$7,Master!$A$71:$K$71,0))),””,HYPERLINK(INDEX(Master!$A$71:$K$272,SMALL(IF(Master!$D$71:$D$272=MID(CELL(“filename”,M2),FIND(“]”,CELL(“filename”,M2))+1,256),ROW(INDIRECT(“$1:$”&COUNTA(Master!$D$71:$D$272)))),ROW(2:2)),MATCH(A$7,Master!$A$71:$K$71,0))))}

        Reply

  • Hello Ashish,
    We have a similar scenario that I would like your help with. We actually have multiple “Master” spreadsheets with information for every school year. We would like a separate tab to contain all of the information from a row based on the contents of one cell. For example, if we identify “R” in a cell, that should copy the contents of that row containing the R into a row on the tab that contains all of the Rs.

    I hope I made sense and thank you in advance for your help!

    Reply

  • Hello Ashish,
    Thank you so much for this workbook! I have a quick question for you about it, though. Is it possible to use structured references in the formula, or are the explicit references required for it to function properly?
    Similarly, is it possible to convert the ranges on the secondary pages to tables so that they are auto-expanding as needed?

    Reply

  • I’ve created a workbook like your one linked above. Master sheet has all rows, turned it into Table to allow for expansion, and added another sheet. Copied your formula into the A2 row and tried to update the formula to fit my data. Nothing pops out into the new sheet. Here’s link to my data, https://www.dropbox.com/scl/fi/f6bz5v7zp82njkfala8nk/DirPrnInfo_CD-s-Cleaned-formulas.xlsx?dl=0&rlkey=b1c6q1t91ynlbd7lwa4d71izp. Looking to add sheets for all variations of column A. How does each sheet account for the additions to the master sheet?

    Reply

    • Hi,
      If you have a Microsoft 365 subscription, then there is a simpler solution.
      In cell A1 of a new sheet, type Electronic. In cell A4, enter this formula
      =FILTER(Master!A2:I12100,Master!A2:A12100=Electronic!A1)
      Now just create a copy of this worksheet and change the name in cell A1
      Hope this helps.

      Reply

  • Hi Ashish,

    Thank you so much for the workbook! It is exactly what I was looking for.

    I have been trying to figure out how to make a spreadsheet with a master tab that lists each person and the kind of poultry they are exhibiting for 4H, and then a tab for each kind of poultry. Each youth can take up to three kinds of poultry, so this means the same name may have to populate on multiple tabs.

    Sometimes it works and other times it does not. I’m sure I have done something to mess up your formula. I would appreciate any help if you have time.

    Thank you again!

    https://docs.google.com/spreadsheets/d/15HYaN69rwdh9vfxZ6yHNPTSfOEQzzDN1/edit?usp=sharing&ouid=109465546187781409377&rtpof=true&sd=true

    Reply

    • Hi,

      Using the Query Editor, select the first and last 2 columns. Right click and select “Unpivot Other Columns”. Click on Close and Load. Now use the formula which i have shared in the Blog.

      Reply

  • Hi!

    I was wondering if you could help me, any info would be greatly appreciated.

    We have three different excel sheets, currently in separate workbooks, that are in Sharepoint and shared with three different groups of individuals. Currently, three different individuals own/created these sheets, and I am one of them. If I merged them all into one master spreadsheet, how would i extract the sheets into different tabs based on the “group name?” And would it be possible for this to be done in a way that if the master sheet was updated, the corresponding tab would also update?

    Or is there an easier way to somehow merge these into one master sheet so that if there’s an update in one sheet, it automatically updates the master sheet?

    Reply

  • sir i want to copy data (its like a data entry cell or column, where i can enter data on daily basis but it could save on another table or column) from a cell or column to another table or sheet with conditionally like if this date equal to that date then copy the data on that date column (note that copy the data – like cut & paste),

    Reply