Linking Multiple Cells to another Sheet with specific criteria

lynette1953

New Member
Joined
Jun 7, 2019
Messages
3
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!!!
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

RasGhul

Well-known Member
Joined
Jul 15, 2016
Messages
609
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

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="font-weight: bold;border-right: 1px solid black;border-bottom: 1px solid black;;">Dates of Service</td><td style="font-weight: bold;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">First Name</td><td style="font-weight: bold;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Last Name</td><td style="font-weight: bold;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Address1</td><td style="font-weight: bold;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Address2</td><td style="font-weight: bold;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Email</td><td style="font-weight: bold;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Website</td><td style="font-weight: bold;text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Mobile</td><td style="font-weight: bold;text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Recovery Dates</td><td style="font-weight: bold;text-align: right;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Cost</td><td style="font-weight: bold;text-align: right;border-bottom: 1px solid black;border-left: 1px solid black;;">Total</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">1/06/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Name1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Last1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Add1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2ADD1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0563C1;;">someone@gmail.com1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0563C1;;">www.mrexcel.com</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">123456</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">1/06/2019</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">100</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">110</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">2/06/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Name2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Last2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Add2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2ADD2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0563C1;;">someone@gmail.com2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0563C1;;">www.mrexcel.com</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">123457</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">50</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">55</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">3/06/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Name3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Last3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Add3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2ADD3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0563C1;;">someone@gmail.com3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0563C1;;">www.mrexcel.com</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">123458</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">200</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">220</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">4/06/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Name4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Last4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Add4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2ADD4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0563C1;;">someone@gmail.com4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0563C1;;">www.mrexcel.com</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">123459</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">5/06/2019</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">300</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">330</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">5/06/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Name5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Last5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Add5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2ADD5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0563C1;;">someone@gmail.com5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0563C1;;">www.mrexcel.com</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">123460</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2/06/2019</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">60</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">66</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">6/06/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Name6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Last6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Add6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2ADD6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0563C1;;">someone@gmail.com6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0563C1;;">www.mrexcel.com</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">123461</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">120</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">132</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">7/06/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Name7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Last7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Add7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2ADD7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0563C1;;">someone@gmail.com7</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0563C1;;">www.mrexcel.com</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">123462</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3/06/2019</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">240</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">264</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">8/06/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Name8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Last8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Add8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2ADD8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0563C1;;">someone@gmail.com8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0563C1;;">www.mrexcel.com</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">123463</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">30</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">33</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">9/06/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Name9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Last9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Add9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2ADD9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0563C1;;">someone@gmail.com9</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0563C1;;">www.mrexcel.com</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">123464</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8/06/2019</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">65</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">71.5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">10/06/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Name10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Last10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Add10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2ADD10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0563C1;;">someone@gmail.com10</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0563C1;;">www.mrexcel.com</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">123465</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">10/09/2019</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">40</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">44</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;;">11/06/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Name11</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Last11</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Add11</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2ADD11</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0563C1;;">someone@gmail.com11</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0563C1;;">www.mrexcel.com</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">123466</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">300</td><td style="text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">330</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="border-top: 1px solid black;border-right: 1px solid black;;">12/06/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">Name12</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">Last12</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">Add12</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">2ADD12</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0563C1;;">someone@gmail.com12</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0563C1;;">www.mrexcel.com</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">123467</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;"></td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-left: 1px solid black;;">56</td><td style="text-align: right;border-top: 1px solid black;border-left: 1px solid black;;">61.6</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br />

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Dates of Service</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">First Name</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Last Name</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Address1</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Address2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0563C1;;">Email</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;text-decoration: underline;color: #0563C1;;">Website</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Mobile</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Recovery Dates</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Cost</td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Total</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2/06/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Name2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Last2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Add2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2ADD2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">someone@gmail.com2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">www.mrexcel.com</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">123457</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">$50.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">$55.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">3/06/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Name3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Last3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Add3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2ADD3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">someone@gmail.com3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">www.mrexcel.com</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">123458</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">$200.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">$220.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">6/06/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Name6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Last6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Add6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2ADD6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">someone@gmail.com6</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">www.mrexcel.com</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">123461</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">$120.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">$132.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">8/06/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Name8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Last8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Add8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2ADD8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">someone@gmail.com8</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">www.mrexcel.com</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">123463</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">$30.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">$33.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">11/06/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Name11</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Last11</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Add11</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2ADD11</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">someone@gmail.com11</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">www.mrexcel.com</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">123466</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">$300.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">$330.00</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">12/06/2019</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Name12</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Last12</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Add12</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">2ADD12</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">someone@gmail.com12</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">www.mrexcel.com</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">123467</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">0</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">$56.00</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">$61.60</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A2</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">A$2:A2</font>)>COUNTIF(<font color="Red">Table1[[Recovery Dates]:[Recovery Dates]],""</font>),"",INDEX(<font color="Red">Table1[Dates of Service],SMALL(<font color="Green">IF(<font color="Purple">Table1[[Recovery Dates]:[Recovery Dates]]="",ROW(<font color="Teal">Table1[[Recovery Dates]:[Recovery Dates]]</font>)-ROW(<font color="Teal">Table1[#Headers]</font>)</font>),ROWS(<font color="Purple">A$2:A2</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">B$2:B2</font>)>COUNTIF(<font color="Red">Table1[[Recovery Dates]:[Recovery Dates]],""</font>),"",INDEX(<font color="Red">Table1[First Name],SMALL(<font color="Green">IF(<font color="Purple">Table1[[Recovery Dates]:[Recovery Dates]]="",ROW(<font color="Teal">Table1[[Recovery Dates]:[Recovery Dates]]</font>)-ROW(<font color="Teal">Table1[#Headers]</font>)</font>),ROWS(<font color="Purple">B$2:B2</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">C$2:C2</font>)>COUNTIF(<font color="Red">Table1[[Recovery Dates]:[Recovery Dates]],""</font>),"",INDEX(<font color="Red">Table1[Last Name],SMALL(<font color="Green">IF(<font color="Purple">Table1[[Recovery Dates]:[Recovery Dates]]="",ROW(<font color="Teal">Table1[[Recovery Dates]:[Recovery Dates]]</font>)-ROW(<font color="Teal">Table1[#Headers]</font>)</font>),ROWS(<font color="Purple">C$2:C2</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">D2</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">D$2:D2</font>)>COUNTIF(<font color="Red">Table1[[Recovery Dates]:[Recovery Dates]],""</font>),"",INDEX(<font color="Red">Table1[Address1],SMALL(<font color="Green">IF(<font color="Purple">Table1[[Recovery Dates]:[Recovery Dates]]="",ROW(<font color="Teal">Table1[[Recovery Dates]:[Recovery Dates]]</font>)-ROW(<font color="Teal">Table1[#Headers]</font>)</font>),ROWS(<font color="Purple">D$2:D2</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">E2</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">E$2:E2</font>)>COUNTIF(<font color="Red">Table1[[Recovery Dates]:[Recovery Dates]],""</font>),"",INDEX(<font color="Red">Table1[Address2],SMALL(<font color="Green">IF(<font color="Purple">Table1[[Recovery Dates]:[Recovery Dates]]="",ROW(<font color="Teal">Table1[[Recovery Dates]:[Recovery Dates]]</font>)-ROW(<font color="Teal">Table1[#Headers]</font>)</font>),ROWS(<font color="Purple">E$2:E2</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">F2</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">F$2:F2</font>)>COUNTIF(<font color="Red">Table1[[Recovery Dates]:[Recovery Dates]],""</font>),"",INDEX(<font color="Red">Table1,SMALL(<font color="Green">IF(<font color="Purple">Table1[[Recovery Dates]:[Recovery Dates]]="",ROW(<font color="Teal">Table1[[Recovery Dates]:[Recovery Dates]]</font>)-ROW(<font color="Teal">Table1[#Headers]</font>)</font>),ROWS(<font color="Purple">F$2:F2</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G2</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">G$2:G2</font>)>COUNTIF(<font color="Red">Table1[[Recovery Dates]:[Recovery Dates]],""</font>),"",INDEX(<font color="Red">Table1[Website],SMALL(<font color="Green">IF(<font color="Purple">Table1[[Recovery Dates]:[Recovery Dates]]="",ROW(<font color="Teal">Table1[[Recovery Dates]:[Recovery Dates]]</font>)-ROW(<font color="Teal">Table1[#Headers]</font>)</font>),ROWS(<font color="Purple">G$2:G2</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">H2</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">H$2:H2</font>)>COUNTIF(<font color="Red">Table1[[Recovery Dates]:[Recovery Dates]],""</font>),"",INDEX(<font color="Red">Table1[Mobile],SMALL(<font color="Green">IF(<font color="Purple">Table1[[Recovery Dates]:[Recovery Dates]]="",ROW(<font color="Teal">Table1[[Recovery Dates]:[Recovery Dates]]</font>)-ROW(<font color="Teal">Table1[#Headers]</font>)</font>),ROWS(<font color="Purple">H$2:H2</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I2</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">I$2:I2</font>)>COUNTIF(<font color="Red">Table1[[Recovery Dates]:[Recovery Dates]],""</font>),"",INDEX(<font color="Red">Table1[Recovery Dates],SMALL(<font color="Green">IF(<font color="Purple">Table1[[Recovery Dates]:[Recovery Dates]]="",ROW(<font color="Teal">Table1[[Recovery Dates]:[Recovery Dates]]</font>)-ROW(<font color="Teal">Table1[#Headers]</font>)</font>),ROWS(<font color="Purple">I$2:I2</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J2</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">J$2:J2</font>)>COUNTIF(<font color="Red">Table1[[Recovery Dates]:[Recovery Dates]],""</font>),"",INDEX(<font color="Red">Table1[Cost],SMALL(<font color="Green">IF(<font color="Purple">Table1[[Recovery Dates]:[Recovery Dates]]="",ROW(<font color="Teal">Table1[[Recovery Dates]:[Recovery Dates]]</font>)-ROW(<font color="Teal">Table1[#Headers]</font>)</font>),ROWS(<font color="Purple">J$2:J2</font>)</font>)</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">K2</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">K$2:K2</font>)>COUNTIF(<font color="Red">Table1[[Recovery Dates]:[Recovery Dates]],""</font>),"",INDEX(<font color="Red">Table1[Total],SMALL(<font color="Green">IF(<font color="Purple">Table1[[Recovery Dates]:[Recovery Dates]]="",ROW(<font color="Teal">Table1[[Recovery Dates]:[Recovery Dates]]</font>)-ROW(<font color="Teal">Table1[#Headers]</font>)</font>),ROWS(<font color="Purple">K$2:K2</font>)</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

lynette1953

New Member
Joined
Jun 7, 2019
Messages
3
[HR][/HR]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

<tbody>
</tbody>
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<today()) spreadsheet.
<TODAY())
I really appreciate your help with this. I feel like I’m on the cusp of understanding all of this, but that I’m missingsomething obvious.

</today())>

 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
44,472
Office Version
365
Platform
Windows
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:

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,857
Messages
5,507,756
Members
408,647
Latest member
Nicho la zido

This Week's Hot Topics

Top