+ Reply to Thread
Results 1 to 2 of 2

Lookup multiple cells that contain a date with an asterisk in a single row

  1. #1
    Registered User
    Join Date
    09-11-2013
    Location
    Portland, Oregon
    MS-Off Ver
    Excel 2011
    Posts
    4

    Exclamation Lookup multiple cells that contain a date with an asterisk in a single row

    I'm doing a long-term research study where I see patients for multiple visits and at two of those visits I collect lab samples (denoted by an * following the visit date). I want to track the dates of the study visits where samples were collected on a separate sheet. Samples are collected based on a date range that is unique to each patient and not at a specific visit so they can occur in any column on the row for each patient.

    Here is an example (absolutely entirely deidentified using only example visit dates)- My current log on Sheet 1 and what I want on Sheet 2:


    Enrollment Example.xlsx

    Please please please help!! I'm pulling my hair out trying to figure this out!

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Lookup multiple cells that contain a date with an asterisk in a single row

    Important, do these steps exactly as explained, the cell that is "highlighted" is important when creating a NAMED FORMULA with relative referencing.

    1) On sheet2, click on B2
    2) Press CTRL-F3 to open the Name Manager
    3) Click on NEW and created the following Named Formulas:

    Name: DataRow
    RefersTo: =OFFSET(Sheet1!$A$1, MATCH(Sheet2!$A2, Sheet1!$A:$A, 0)-1, 1, , 20)

    Name: EarlyLab
    RefersTo: =SUBSTITUTE(INDEX(DataRow, MIN(IF(ISNUMBER(SEARCH("~*", DataRow)), COLUMN(DataRow)-1))),"*", "")+0

    Name: LateLab
    RefersTo: =SUBSTITUTE(INDEX(DataRow, MAX(IF(ISNUMBER(SEARCH("~*", DataRow)), COLUMN(DataRow)-1))),"*","")+0

    4) Now enter this formula in B2: =EarlyLab

    5) enter this formula in C2: =IF(LateLab=EarlyLab, "", LateLab)

    6) Copy B2:C2 down
    Attached Files Attached Files
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Select multiple rows and cells based on a single date
    By UTLee in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-16-2013, 11:09 AM
  2. Replies: 10
    Last Post: 02-19-2013, 12:05 PM
  3. Sum values from multiple cells - multiple lookup values in single cell
    By taxdept in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-21-2012, 04:12 PM
  4. Replies: 9
    Last Post: 03-22-2010, 05:16 PM
  5. Replies: 1
    Last Post: 07-25-2006, 09:45 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1