Results 1 to 4 of 4

Thread: Dynamically Pulling Data between Worksheets
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Feb 2018
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Dynamically Pulling Data between Worksheets

    Hello,

    I am trying to do a small pull of data between two worksheets but in an active manner, I'm hoping someone here might be able to figure this one out as I can't come up with a perfect solution.

    Sample Data on Worksheet1:



    On Worksheet2 I would like to pull anything that says 'Yes' in Worksheet1's Column A - this is using a data validation list of Yes/No

    Desired results for Worksheet2:



    I would like this to be dynamic so that anything switched to Yes shows up on Worksheet2 and when it switches to No, it disappears from Worksheet2. I understand that something like this could be done in Access but I would like to keep this in Excel if possible.

    Thank you

  2. #2
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    225
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Dynamically Pulling Data between Worksheets

    Hello,

    If this is Worksheet1

    A B C D
    1 Compile Manufacturer Product Price
    2 Yes White Sky Inc. Solar flashlight $ 22.83
    3 No Easy Partners Rubber ladder $ 12.32
    4 No ACME Widgets Cat trainer $ 9.00
    5 Yes Hope & Sky Inc. Tissue umbrella $ 230.22
    Worksheet1
    Then this formulae should work:
    =IF(COUNTIF(Worksheet1!$A$2:$A$20,"Yes") < ROWS(A$1:A1),"",INDEX(Worksheet1!A$2:A$20,AGGREGATE(15,6,ROW(Worksheet1!$A$2:$A$20)-ROW(Worksheet1!$A$1)/(Worksheet1!$A$2:$A$20="Yes"),ROWS(A$1:A1))))

    Put it in Worksheet2 cell A2. Change the $20 to the maximum row number you'll ever have (say $9999) and copy it down that many rows. Now highlight those cells and copy across the number of columns where you have data.

    A B C D
    1 Compile Manufacturer Product Price
    2 Yes White Sky Inc. Solar flashlight 22.83
    3 Yes Hope & Sky Inc. Tissue umbrella 230.22
    4
    Worksheet2

    Worksheet Formulas
    Cell Formula
    A2 =IF(COUNTIF(Worksheet1!$A$2:$A$20,"Yes") < ROWS(A$1:A1),"",INDEX(Worksheet1!A$2:A$20,AGGREGATE(15,6,ROW(Worksheet1!$A$2:$A$20)-ROW(Worksheet1!$A$1)/(Worksheet1!$A$2:$A$20="Yes"),ROWS(A$1:A1))))
    B2 =IF(COUNTIF(Worksheet1!$A$2:$A$20,"Yes") < ROWS(B$1:B1),"",INDEX(Worksheet1!B$2:B$20,AGGREGATE(15,6,ROW(Worksheet1!$A$2:$A$20)-ROW(Worksheet1!$A$1)/(Worksheet1!$A$2:$A$20="Yes"),ROWS(B$1:B1))))

  3. #3
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,808
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: Dynamically Pulling Data between Worksheets

    The first part of your request would be simple:
    But the second part about deleting the row from Sheet(2) would be more complicated.
    Will the value on Sheet(2) column A change to "No" or did you mean if the value in column A of sheet(1) changes to "No"

    If it's when value on sheet(1) changes to "No" how would we know what row on sheet(2) to Delete.
    Is their any unique value on the row to identify the row.
    If you mean when "No" in entered into column A of Sheet(2) it would be simple.


    Here is the way to do the first part:

    This is an auto sheet event script
    Your Workbook must be Macro enabled
    To install this code:
    Right-click on the sheet (1) Tab
    Select View Code from the pop-up context menu
    Paste the code in the VBA edit window

    When you enter "Yes" into column A of Sheet(1) the script will run.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    'Modified  8/25/2019  12:49:26 AM  EDT
    If Target.Column = 1 And Target.Value <> "" Then
    If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
    Dim lastrow As Long
    lastrow = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row + 1
        Select Case Target.Value
            Case "Yes"
            Rows(Target.Row).Copy Sheets(2).Rows(lastrow)
            Case "No"
        
        End Select
    End If
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  4. #4
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    40,974
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Dynamically Pulling Data between Worksheets

    I would take a somewhat similar approach to Toadstool but there are quite a lot of things being calculated multiple time in that suggestion so I have used a helper column & helper cell to save calculating so much.

    Here is my Worksheet1 with just some random numbers as dummy data in columns B:G (We cannot copy your sample data from images like you posted. See my signature block below for help on how to post data that can be copied - like Toadstool & I have done in this thread)

    Column H is my helper column but it could be any column & it could be hidden after entering the formulas.
    Cell H1 is left empty
    Formula in H2 is copied down to the end of your data or the end of wherever your data might eventually come to.

    Worksheet1

    ABCDEFGH
    2No8324714568800
    3No502112995510
    4Yes893311953531
    5Yes5676899896502
    6No1152243189972
    7Yes9353251231593
    8No3340655197503
    9Yes563508743434
    10No3019848330984
    11Yes572787968435
    12No76747361585
    13No13374799205
    14Yes109587645306
    15No881428262586
    16Yes41890693407
    17No653625642557
    18No9020121286147
    19No81546430277
    20 7
    21 7

    Spreadsheet Formulas
    CellFormula
    H2=H1+(A2="Yes")


    Excel tables to the web >> Excel Jeanie HTML 4


    In Worksheet2 I have used cell Z1 as a helper cell. Again it could be another cell & the column can be hidden if you want.
    Formula in A2 is copied down as far as you might ever need.
    Formula in B2 is copied (after adjusting the max row $1000 if needed) across and down as far as column A was.

    Worksheet2

    ABCDEFGHIZ
    1 7
    2Yes89331195353
    3Yes567689989650
    4Yes935325123159
    5Yes56350874343
    6Yes57278796843
    7Yes10958764530
    8Yes4189069340
    9
    10

    Spreadsheet Formulas
    CellFormula
    Z1=LOOKUP(9.99E+307,Worksheet1!H:H)
    A2=IF(ROWS(A$2:A2)>Z$1,"","Yes")
    B2=IF($A2="","",INDEX(Worksheet1!B$2:B$1000,MATCH(ROWS(A$2:A2),Worksheet1!$H$2:$H$1000,0)))


    Excel tables to the web >> Excel Jeanie HTML 4
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •