Return list of results. from a list, if cell value is blank

[TheOnlyJme]

Hi,

I'm helping a friend improve his patient list (He works for the National Health Service in the UK).

He wants a 'view' of his patient list that only shows current patients (i.e. a second sheet with duplicate data, if the patient's discharge date is not set (blank)).

The patient lists contains 20 columns of data (name, ID, condition, discharge date etc). The team don't want to use filters but would like to have a duplicate sheet (with identical look and feel) that only shows patients who are still under there care, based on the fact that there is no discharge date. Sheet 1=Patients Sheet, 2=Current Patients, Column I contains 'Date of discharge from hospital'.

I thought I should be able to achieve this using Vlookup with Match and possibly If. But can't find the right combination of functions to use.

Due to security, I'm not able to use VBA.

Thanks in advance!!
Jamie

 

[TheOnlyJme]

If it helps the layout is something like this:

Name	Patient ID	DOB	Age	Sex	Admission Date	Referral Date	1st Treatment Date	Discharge Date
Jamie	123454	1/12/70	42	M	06/1/18	08/1/18	09/1/18	07/2/18
Paula	876545	18/8/79	37	F	12/1/18	12/1/18	13/1/18
Carl	75664	31/8/80	36	M	19/1/18	20/1/18	20/1/18	30/1/18

<tbody>
</tbody>

So in this example I only want Paula to show up on the 'view' sheet and then underneath all the other patients with a blank Discharge Date.