Linking Multiple Cells to another Sheet with specific criteria

[lynette1953]

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!!!

 

[RasGhul]

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

Book1
	B	C	D	E	F	G	H	I	J	K	L
2	Dates of Service	First Name	Last Name	Address1	Address2	Email	Website	Mobile	Recovery Dates	Cost	Total
3	1/06/2019	Name1	Last1	Add1	2ADD1	someone@gmail.com1	www.mrexcel.com	123456	1/06/2019	100	110
4	2/06/2019	Name2	Last2	Add2	2ADD2	someone@gmail.com2	www.mrexcel.com	123457		50	55
5	3/06/2019	Name3	Last3	Add3	2ADD3	someone@gmail.com3	www.mrexcel.com	123458		200	220
6	4/06/2019	Name4	Last4	Add4	2ADD4	someone@gmail.com4	www.mrexcel.com	123459	5/06/2019	300	330
7	5/06/2019	Name5	Last5	Add5	2ADD5	someone@gmail.com5	www.mrexcel.com	123460	2/06/2019	60	66
8	6/06/2019	Name6	Last6	Add6	2ADD6	someone@gmail.com6	www.mrexcel.com	123461		120	132
9	7/06/2019	Name7	Last7	Add7	2ADD7	someone@gmail.com7	www.mrexcel.com	123462	3/06/2019	240	264
10	8/06/2019	Name8	Last8	Add8	2ADD8	someone@gmail.com8	www.mrexcel.com	123463		30	33
11	9/06/2019	Name9	Last9	Add9	2ADD9	someone@gmail.com9	www.mrexcel.com	123464	8/06/2019	65	71.5
12	10/06/2019	Name10	Last10	Add10	2ADD10	someone@gmail.com10	www.mrexcel.com	123465	10/09/2019	40	44
13	11/06/2019	Name11	Last11	Add11	2ADD11	someone@gmail.com11	www.mrexcel.com	123466		300	330
14	12/06/2019	Name12	Last12	Add12	2ADD12	someone@gmail.com12	www.mrexcel.com	123467		56	61.6
Sheet1

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Dates of Service	First Name	Last Name	Address1	Address2	Email	Website	Mobile	Recovery Dates	Cost	Total
2	2/06/2019	Name2	Last2	Add2	2ADD2	someone@gmail.com2	www.mrexcel.com	123457	0	$50.00	$55.00
3	3/06/2019	Name3	Last3	Add3	2ADD3	someone@gmail.com3	www.mrexcel.com	123458	0	$200.00	$220.00
4	6/06/2019	Name6	Last6	Add6	2ADD6	someone@gmail.com6	www.mrexcel.com	123461	0	$120.00	$132.00
5	8/06/2019	Name8	Last8	Add8	2ADD8	someone@gmail.com8	www.mrexcel.com	123463	0	$30.00	$33.00
6	11/06/2019	Name11	Last11	Add11	2ADD11	someone@gmail.com11	www.mrexcel.com	123466	0	$300.00	$330.00
7	12/06/2019	Name12	Last12	Add12	2ADD12	someone@gmail.com12	www.mrexcel.com	123467	0	$56.00	$61.60
Sheet2

Cell Formulas
Range	Formula
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,SMALL(IF(Table1[[Recovery Dates]:[Recovery Dates]]="",ROW(Table1[[Recovery Dates]:[Recovery Dates]])-ROW(Table1[#Headers])),ROWS(F$2:F2))))}[/COLOR][/XD][/XR][XR][XD]G2[/XD][XD][COLOR=red]{=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))))}[/COLOR][/XD][/XR][XR][XD]H2[/XD][XD][COLOR=red]{=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))))}[/COLOR][/XD][/XR][XR][XD]I2[/XD][XD][COLOR=red]{=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))))}[/COLOR][/XD][/XR][XR][XD]J2[/XD][XD][COLOR=red]{=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))))}[/COLOR][/XD][/XR][XR][XD]K2[/XD][XD][COLOR=red]{=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))))}[/COLOR][/XD][/XR][XR][XD=cs:2][COLOR=red]Press CTRL+SHIFT+ENTER to enter array formulas.[/COLOR][/XD][/XR][/RANGE]

 

[lynette1953]

[TABLE="width: 500"]
<tbody>[TR]
[TD]

---

B

[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[TD]K
[/TD]
[TD]L
[/TD]
[/TR]
[TR]
[TD]Appt Dt
[/TD]
[TD]Beg Tm
[/TD]
[TD]Fee Slip
[/TD]
[TD]Pat Name
[/TD]
[TD]Md Rc
[/TD]
[TD]Event
[/TD]
[TD]Rendering
[/TD]
[TD]Sts
[/TD]
[TD]Recovery Date
[/TD]
[TD]Other Services
[/TD]
[TD]Notes
[/TD]
[/TR]
[TR]
[TD]5/31/2019
[/TD]
[TD]10:30A
[/TD]
[TD]Y
[/TD]
[TD]Doe, John
[/TD]
[TD]1234567
[/TD]
[TD]Office Visit 15
[/TD]
[TD]Smith MD, Jane
[/TD]
[TD]Kept
[/TD]
[TD]6/10/2019
[/TD]
[TD]Xray
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/31/2019
[/TD]
[TD]11:00A
[/TD]
[TD][/TD]
[TD]Brown, James
[/TD]
[TD]1234568
[/TD]
[TD]Office Visit 15
[/TD]
[TD]Smith MD, Jane
[/TD]
[TD]Kept
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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())>

 

[lynette1953]

=and($j2=””,$b2 < today())

 

[Fluff]

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