Which formula to use for this...

Hi

I have a worksheet with a list of daily patients that I see. The details that I am interested in(from the many columns) are: Date, Patient Name, Diagnosis, Follow-up Date, Notes. These are the columns I am interested in and they are not contiguous. As can be guessed, the Date column will be in chronological order. The Follow-up date will have no chronology because some patients may be called after a week, some after 2 weeks, some after a month and some after 3 months.

What I need to do is, set up a filter in such a way that on Sheet 2 of the workbook, if I enter a date say todays' date in cell C2, the data from sheet 1 which match the follow-up dates of Sheet2!$C$2 must be listed in Sheet 2. This will enable me to see the possible appointments I have on any given day without going to Filter Data routine every now and then. So in a nutshell, given a date in the said cell, look up all values of column "Follow-up Date" in Sheet1 and copy the matching records to Sheet2. Is there a way to do this?

Thanks.

Try this

Bob
That worked perfectly. Thanks a lot. Can you please explain to me how the array formula that you have written works? Thanks again. Appreciate the effort.

Okay, I'll have a go. I'll chop the range down to 2:20 instead of 2:200 to help show the details.

The main logic is a simple IF test that checks whether the follow-up dates on Patient List equal the user defined date

IF('Patient List'!$D$2:$D$20=$C$2,ROW('Patient List'!$D$2:$D$20))

this builds an array of matching row numbers where the dates match, or FALSE where they do not. In our exampl, with a defined date of 8th Aug, this creates an array of

{2;FALSE;FALSE;FALSE;6;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

The formula then takes the next smallest value from that array

SMALL(IF('Patient List'!$D$2:$D$20=$C$2,ROW('Patient List'!$D$2:$D$20)),ROW(A1))

We use the SMALL function as that can get the nth smallest values, MIN only gets the very smallest, and we use the variable ROW(A1) to get the 1st smallest, then the 2nd smallest, ..., because A1 increments to A2 on the second row, and so on. So this returns a value of
{2}, then
{6}, and then a series of
{FALSE}

The ISERROR checks this returned value, so if we have an error we output "", just to keep it looking good. If it is not an error, we repeat the SMALL(IF(... to return that row number. This is saved in column A with a white font to hide it.

Finally, we use a simple INDEX(range, row_num) to get the actual value, row_num being the value in column A, range being the variable column, A:A, B:B, etc.

Hello Bob,
You are the Super Doctor Excel Guru.
Thanks

Bob

That was the best I could have hoped for. Your explanation was really better than what I tried to read in the Excel Help. I had posted this question in another Excel forum too before I found this site. And NO ONE has replied yet! I am sure to return to this forum for my excel questions. Am just going to post a new one.

Thanks.

hercule_p2001,

I'm really glad that you got your answer here, but can you do me a favour? Please make sure that you go back to the other forum and post that you got an answer here. The reason is not so much that I want links coming here, but I respect the time of other experts out there, and we don't want to waste their time and effort if an answer has been solved. Here's a little more on why: http://www.excelguru.ca/forums/faq.php?faq=crossposting

Again, very glad you got your answer, and hope to see much more of you here.

Thanks. Done.