Linking Multiple Cells to another Sheet with specific criteria
Results 1 to 5 of 5

Thread: Linking Multiple Cells to another Sheet with specific criteria
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Linking Multiple Cells to another Sheet with specific criteria

    I am having difficulty finding the best way to link cells from one sheet to another. I want to keep the rows together and only transfer info that has a blank recovery date. I have Columns B-L and 305 rows. In Column B, I have Dates of Service with other identifiable info in the columns to follow. I need to only link the rows that do NOT possess a Recovery Date (Column J) and is blank. Those are the tickets that are still missing. I want to link those missing tickets and their corresponding rows to another sheet. I've explored Advanced Filter options, Get External Data from Other Sources, and have experimented with different formulas that I have found online. Nothing seems to get me what I'm looking for. To add to my frustration...I tried to find a way to attach a screenshot on here to no avail. I am very new to VBA codes and the world of advanced Excel techniques and could really use the help!!!

  2. #2
    Board Regular
    Join Date
    Jul 2016
    Posts
    496
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Linking Multiple Cells to another Sheet with specific criteria

    Hi Lynette

    Just to clarify you only want to return rows of data that have a blank cell in Column J, maybe this. I'm flying blind with your actual info but the solution is the same. In my example your data is in Sheet1 and returning rows in Sheet2.

    If you use a table formula version you can add more than 305 rows without changing your formula.

    1. Convert your data to a table with CTRL+T
    2. Copy each formula into each column and drag down 1000 rows, so you can add up to 1000 rows of data from Sheet1


    BCDEFGHIJKL
    2Dates of ServiceFirst NameLast NameAddress1Address2EmailWebsiteMobileRecovery DatesCostTotal
    31/06/2019Name1Last1Add12ADD1someone@gmail.com1www.mrexcel.com1234561/06/2019100110
    42/06/2019Name2Last2Add22ADD2someone@gmail.com2www.mrexcel.com1234575055
    53/06/2019Name3Last3Add32ADD3someone@gmail.com3www.mrexcel.com123458200220
    64/06/2019Name4Last4Add42ADD4someone@gmail.com4www.mrexcel.com1234595/06/2019300330
    75/06/2019Name5Last5Add52ADD5someone@gmail.com5www.mrexcel.com1234602/06/20196066
    86/06/2019Name6Last6Add62ADD6someone@gmail.com6www.mrexcel.com123461120132
    97/06/2019Name7Last7Add72ADD7someone@gmail.com7www.mrexcel.com1234623/06/2019240264
    108/06/2019Name8Last8Add82ADD8someone@gmail.com8www.mrexcel.com1234633033
    119/06/2019Name9Last9Add92ADD9someone@gmail.com9www.mrexcel.com1234648/06/20196571.5
    1210/06/2019Name10Last10Add102ADD10someone@gmail.com10www.mrexcel.com12346510/09/20194044
    1311/06/2019Name11Last11Add112ADD11someone@gmail.com11www.mrexcel.com123466300330
    1412/06/2019Name12Last12Add122ADD12someone@gmail.com12www.mrexcel.com1234675661.6

    Sheet1





    ABCDEFGHIJK
    1Dates of ServiceFirst NameLast NameAddress1Address2EmailWebsiteMobileRecovery DatesCostTotal
    22/06/2019Name2Last2Add22ADD2someone@gmail.com2www.mrexcel.com1234570$50.00$55.00
    33/06/2019Name3Last3Add32ADD3someone@gmail.com3www.mrexcel.com1234580$200.00$220.00
    46/06/2019Name6Last6Add62ADD6someone@gmail.com6www.mrexcel.com1234610$120.00$132.00
    58/06/2019Name8Last8Add82ADD8someone@gmail.com8www.mrexcel.com1234630$30.00$33.00
    611/06/2019Name11Last11Add112ADD11someone@gmail.com11www.mrexcel.com1234660$300.00$330.00
    712/06/2019Name12Last12Add122ADD12someone@gmail.com12www.mrexcel.com1234670$56.00$61.60

    Sheet2



    Array Formulas
    CellFormula
    A2{=IF(ROWS(A$2:A2)>COUNTIF(Table1[[Recovery Dates]:[Recovery Dates]],""),"",INDEX(Table1[Dates of Service],SMALL(IF(Table1[[Recovery Dates]:[Recovery Dates]]="",ROW(Table1[[Recovery Dates]:[Recovery Dates]])-ROW(Table1[#Headers])),ROWS(A$2:A2))))}
    B2{=IF(ROWS(B$2:B2)>COUNTIF(Table1[[Recovery Dates]:[Recovery Dates]],""),"",INDEX(Table1[First Name],SMALL(IF(Table1[[Recovery Dates]:[Recovery Dates]]="",ROW(Table1[[Recovery Dates]:[Recovery Dates]])-ROW(Table1[#Headers])),ROWS(B$2:B2))))}
    C2{=IF(ROWS(C$2:C2)>COUNTIF(Table1[[Recovery Dates]:[Recovery Dates]],""),"",INDEX(Table1[Last Name],SMALL(IF(Table1[[Recovery Dates]:[Recovery Dates]]="",ROW(Table1[[Recovery Dates]:[Recovery Dates]])-ROW(Table1[#Headers])),ROWS(C$2:C2))))}
    D2{=IF(ROWS(D$2:D2)>COUNTIF(Table1[[Recovery Dates]:[Recovery Dates]],""),"",INDEX(Table1[Address1],SMALL(IF(Table1[[Recovery Dates]:[Recovery Dates]]="",ROW(Table1[[Recovery Dates]:[Recovery Dates]])-ROW(Table1[#Headers])),ROWS(D$2:D2))))}
    E2{=IF(ROWS(E$2:E2)>COUNTIF(Table1[[Recovery Dates]:[Recovery Dates]],""),"",INDEX(Table1[Address2],SMALL(IF(Table1[[Recovery Dates]:[Recovery Dates]]="",ROW(Table1[[Recovery Dates]:[Recovery Dates]])-ROW(Table1[#Headers])),ROWS(E$2:E2))))}
    F2{=IF(ROWS(F$2:F2)>COUNTIF(Table1[[Recovery Dates]:[Recovery Dates]],""),"",INDEX(Table1[Email],SMALL(IF(Table1[[Recovery Dates]:[Recovery Dates]]="",ROW(Table1[[Recovery Dates]:[Recovery Dates]])-ROW(Table1[#Headers])),ROWS(F$2:F2))))}
    G2{=IF(ROWS(G$2:G2)>COUNTIF(Table1[[Recovery Dates]:[Recovery Dates]],""),"",INDEX(Table1[Website],SMALL(IF(Table1[[Recovery Dates]:[Recovery Dates]]="",ROW(Table1[[Recovery Dates]:[Recovery Dates]])-ROW(Table1[#Headers])),ROWS(G$2:G2))))}
    H2{=IF(ROWS(H$2:H2)>COUNTIF(Table1[[Recovery Dates]:[Recovery Dates]],""),"",INDEX(Table1[Mobile],SMALL(IF(Table1[[Recovery Dates]:[Recovery Dates]]="",ROW(Table1[[Recovery Dates]:[Recovery Dates]])-ROW(Table1[#Headers])),ROWS(H$2:H2))))}
    I2{=IF(ROWS(I$2:I2)>COUNTIF(Table1[[Recovery Dates]:[Recovery Dates]],""),"",INDEX(Table1[Recovery Dates],SMALL(IF(Table1[[Recovery Dates]:[Recovery Dates]]="",ROW(Table1[[Recovery Dates]:[Recovery Dates]])-ROW(Table1[#Headers])),ROWS(I$2:I2))))}
    J2{=IF(ROWS(J$2:J2)>COUNTIF(Table1[[Recovery Dates]:[Recovery Dates]],""),"",INDEX(Table1[Cost],SMALL(IF(Table1[[Recovery Dates]:[Recovery Dates]]="",ROW(Table1[[Recovery Dates]:[Recovery Dates]])-ROW(Table1[#Headers])),ROWS(J$2:J2))))}
    K2{=IF(ROWS(K$2:K2)>COUNTIF(Table1[[Recovery Dates]:[Recovery Dates]],""),"",INDEX(Table1[Total],SMALL(IF(Table1[[Recovery Dates]:[Recovery Dates]]="",ROW(Table1[[Recovery Dates]:[Recovery Dates]])-ROW(Table1[#Headers])),ROWS(K$2:K2))))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself


    - forum use guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

  3. #3
    New Member
    Join Date
    Jun 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Linking Multiple Cells to another Sheet with specific criteria


    B
    C D E F G H I J K L
    Appt Dt Beg Tm Fee Slip Pat Name Md Rc Event Rendering Sts Recovery Date Other Services Notes
    5/31/2019 10:30A Y Doe, John 1234567 Office Visit 15 Smith MD, Jane Kept 6/10/2019 Xray
    5/31/2019 11:00A Brown, James 1234568 Office Visit 15 Smith MD, Jane Kept
    Thanks so much for your reply!!! I have some questions though. First of all, how do I add these formulas in? Is this just added to the column header using the options to the left of the text (the one with the X, the checkmark, and the fx)? Or is this applied using conditional formatting?

    Secondly, I just wanted to clarify what my table looks like to assist in the formula setup. I have a spreadsheet for each day showing what appts were Kept. I then have to verify for accuracy using fee slips collected for that date. Fee slips that are missing I have to track down and verify if patient actually showed for that date. If the fee slips are missing after 1 week, then I would like for these appts to be marked in red and moved to another spreadsheet to start investigation. Each date will have a different number of rows. I have found a formula using conditional formatting to mark rows in red based on whether there is a blank in the Recovery Date column, but I have to manually apply it after 1 week (which is fine but it would be great if I could make this happen all at once). =AND($J2=,$B2
    I really appreciate your help with this. I feel like Im on the cusp of understanding all of this, but that Im missingsomething obvious.



  4. #4
    New Member
    Join Date
    Jun 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Linking Multiple Cells to another Sheet with specific criteria

    =and($j2=””,$b2 < today())
    Last edited by Fluff; Jun 10th, 2019 at 01:42 PM.

  5. #5
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,439
    Post Thanks / Like
    Mentioned
    443 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Linking Multiple Cells to another Sheet with specific criteria

    You need to add a space either side of < and >. Otherwise the software thinks you're posting HTML code & treats it as such.

    I have amended your formula for you
    Last edited by Fluff; Jun 10th, 2019 at 01:42 PM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

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
  •