Solved

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

Posted on 2016-08-04
6
66 Views
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.
RFQ-Status-Report-TEMPLATE.xlsx
0
Comment
Question by:Marcia Morris
6 Comments
 
LVL 28

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")
            dws.Columns.AutoFit
        End If
    End With
    Set dws = Nothing
Next tCell
sws.AutoFilterMode = 0
sws.Activate
Application.DisplayAlerts = False
Temp.Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
MsgBox "Task Completed.", vbInformation, "Done!"
End Sub

Open in new window

RFQ-Status-Report-TEMPLATE.xlsm
0
 
LVL 21

Accepted Solution

by:
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.
RFQ-Status-Report-TEMPLATE.xlsx
0
 

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.
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 31

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.

Thanks
Rob H
0
 

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.
0
 
LVL 21

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.
RFQ-Status-Report-TEMPLATE.xlsx
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now