How can I extract data from one tab on a specific individuals to numerous tabs in Excel?

Posted on 2016-08-04
Last Modified: 2016-08-12
We have a sales admin that downloads data from our system daily and then what she was doing was filtering by Engineer, then cutting and pasting in each individual tab the information associated with each specific ENG (takes her several hours to complete the entire process DAILY). I've linked the fields from the TOTAL tabs to all of the individual tabs with Engineer names. Is there a formula that can take the information associated with the specific assigned ENG (column K in TOTAL tab) and filter it out to the other tabs? They don't want them filtering from the main TOTAL tab. And the ENG said it's easier if they can just go their tab and look at their projects.  I have a couple of other things I would like to do to the spreadsheet to make her job easier but I'm starting with this first. Once I complete this step, I will ask the other questions.
Question by:Marcia Morris
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41743667
Hi Marcia,

I suggest you to go for a VBA solution instead of formulas because as the data grows and with Array formulas on all the sheets, the file size will increase unnecessarily and slow down the sheets calculation.

If you are open to a VBA solution, please try this....

The problem right now is that in Col. K on Total sheet, you have abbreviated names of Engineers while you have named all the engineers sheets with their full name. There is no way for either a formula of a code to know their full name.
It's better to have a lookup table on a sheet so that the code can find their full names.
The code given below will create the sheets for all the engineers listed in col. A and transfer their own data to their own sheets.
You may click the button called "Copy Data To Individual Sheets" on the Total Sheet to run the code.

If you have difficulty in downloading and opening the attached file due to a temporary bug in the forum, first download and save it on your system and then open it.

Since this is a macro enabled workbook, you will need to enable the macros when prompted to run the code.

Sub TransferDataToRelevantSheets()
Dim sws As Worksheet, dws As Worksheet, Temp As Worksheet
Dim slr As Long, dlr As Long, tlr As Long
Dim sRng As Range, sCell As Range, tRng As Range, tCell As Range
Dim Engineer As String

Application.ScreenUpdating = False

Set sws = Sheets("Total")
slr = sws.Cells(Rows.Count, 1).End(xlUp).Row
If slr < 13 Then
    MsgBox "There is no data on Total Tab.", vbCritical, "Data Not Found!"
    Exit Sub
End If
Sheets.Add(before:=Sheets(1)).Name = "Temp"
Set Temp = ActiveSheet
sws.Range("K13:K" & slr).Copy Temp.Range("A1")
Temp.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo
tlr = Temp.Cells(Rows.Count, 1).End(xlUp).Row + 1
Set tRng = Temp.Range("A1:A" & tlr)
For Each tCell In tRng
    With sws.Rows(12)
        .AutoFilter field:=11, Criteria1:=tCell
        If sws.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Rows.Count > 1 Then
            On Error Resume Next
            Set dws = Sheets(tCell.Value)
            dlr = dws.Cells(Rows.Count, 1).End(xlUp).Row
            If dlr > 11 Then dws.Rows("12:" & dlr).Clear
            On Error GoTo 0
            If dws Is Nothing Then
                Sheets.Add after:=Sheets(Sheets.Count)
                If tCell = "" Then tCell.Value = "UnAssigned"
                ActiveSheet.Name = tCell.Value
                Set dws = ActiveSheet
            End If
            sws.Range("A12").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy dws.Range("A12")
        End If
    End With
    Set dws = Nothing
Next tCell
sws.AutoFilterMode = 0
Application.DisplayAlerts = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "Task Completed.", vbInformation, "Done!"
End Sub

Open in new window

LVL 22

Accepted Solution

Ejgil Hedegaard earned 500 total points
ID: 41744321
Here is a solution using only 2 calculations (array formulas) on each sheet, to set the start and end row for the engineer.
The values are then retrieved with index formulas, and index is so so fast you will not notice the calculation.
File size is only 744 kB.

The engineer "name" is in A3, and if none I put an x, because not assigned is empty on Total.
First row in A4, and last row in A5.
The index start at row 13, so 1 means the start row for the index range, not the row on the sheet.
It is required that Total sheet is sorted by Engineer first, to ensure that it is possible to set a start and end row for each engineer.

Author Comment

by:Marcia Morris
ID: 41746537
Ejgil.....thank you so much!!!! It worked like a charm. I am going to do a run through with the Sales Admin tomorrow since she was out Friday. Then I will proceed with asking part 2 of my question which has to do with one of the other tab she had in the spreadsheet that I wasn't clear on.
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

LVL 33

Expert Comment

by:Rob Henson
ID: 41746812
Is the data in a format that can be summarised in a Pivot Table?

If so, you can create a pivot table with Engineer as the Page/Report filter and there is a setting whereby it will create a tab for each Page.

Rob H

Author Comment

by:Marcia Morris
ID: 41753385
Ejgil..The Sales Admin reviewed the spreadsheet and asked for some additional items that she was also doing manually.
  1. 1. I was trying to include a conditional format to make the entire row of of each tab highlight yellow if it contains the text "large package". I don't know what I am not doing correct in the 'total' tab, but it only highlighted the cell with the words large package.
  2. 2. Is it possible to identify all line items in the "Total" tab with a Bid Due Date (column I) of {3 weeks ago from today → yesterday’s date} Example: Today is 8/11, so the late range would be 7/20 – 8/10, and extract these to the "Late Tab"? They already show up in each Estimator tab but they want to see what's late in one tab. Basically every day they look at what's late from the system they export the data out of.
LVL 22

Expert Comment

by:Ejgil Hedegaard
ID: 41754105
1. Columns A:N highlighted for rows containing "Large Package" in column E.

2. Formula in column O on Total identify the late rows, setting an increasing number to use on the Late tab.

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question