Results 1 to 8 of 8

Thread: Which formula to use for this...

  1. #1

    Which formula to use for this...



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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.

  2. #2
    Try this
    Attached Files Attached Files

  3. #3
    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.

  4. #4
    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.
    Last edited by Bob Phillips; 2011-08-09 at 01:52 PM.

  5. #5
    Seeker jpr73's Avatar
    Join Date
    Apr 2011
    Location
    France
    Posts
    15
    Articles
    0
    Hello Bob,
    You are the Super Doctor Excel Guru.
    Thanks

  6. #6
    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.

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    Thanks. Done.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •