-
How to match clinical trials to subjects based on eligibility criteria columns?
Hello, I have 2 sheets and I want to pull information from sheet to another sheet if certain criteria are met. The first sheet, "Slot List", is a list of clinical trials and the columns contain the study name and certain eligibility criteria patients need to meet to join the study. On the second sheet, "Trial Match", is a…
-
Formula not returning the correct values
I'm having a bit of a weird issue. I have a helper column that is supposed to return values that are pulled for a metrics sheet. Overall the formula is working, but it's like its not moving past the "Materials Delivered to Vendor" part. =IF([GM Status]@row = "PO Created", "Written", IF([GM Status]@row = "Delivered to…
-
Subtract 1 month from the date expire for a task
I have a column that calculates a due date that uses this simple formula: =DATE(YEAR([Date Expired]@row), MONTH([Date Expired]@row) - 1, DAY([Date Expired]@row)) It works great but for some reason returns #INVALID VALUE when the date expired is any day in January. any other month seems to be fine I'm guessing because the…
-
I need help rolling up multiple weeks of data into one response based on specified criteria
I currently have a sheet that collects weekly attendance info from multiple stakeholders (for the purpose of this question we can just look at the Coach Info columns). There are 3 responses that can be put into this column: 'Yes', 'Some, and 'None'. My second sheet is a monthly tracker in which I am trying to read four…
-
Getting #DIVIDE BY ZERO error message for AVG + COLLECT formula (trying to exclude zeros/blanks)
I added a Curriculum Average row (blue row in the screenshot) to help roll the data up before I run an AVG(COLLECT formula from my master data sheet. I've covered up teammate information for privacy. Currently, I am referencing the respective Region & COURSE2000 Curriculum Average in the formula below. There are COURSE2000…
-
Formula issue
Hi Brian Trust My formula wont work LOGIC If the Helper Date Column is blank then the priority cell is blank If the Helper Date Column is less than 10 days, its Low Priority If the Helper Date Column is greater than 10 days, its High Priority? FORMULA =IF(ISBLANK([DD 10 Day - Helper Column]@row), "",…
-
#NO MATCH result with INDEX/MATCH formula referencing Auto-Number/System
I use the INDEX/MATCH formula across many of my sheets for many years. This is the first year I've encountered this issue: my formula is indexing information referencing an auto-number system column type. We generate 3-digit "reference IDs" in this auto-number column to make it easy to index information for that respective…
-
VLookup from multi select "Search Value"
=VLOOKUP(Preceptor@row, {VLookUp for Preceptors MCE Name}, 5, false) Trying to pull one answer from a search value that could have multiple options. Preceptor@row is a multi select drop down, and I only need to pull one value from any of the possible options in the preceptor@row (because it will be the same no matter who…
-
I need help creating a sheet that will track all updates made to a weekly status field.
I have a master sheet that has the name of every project and specific project information on it. I also have a metadata sheet that pulls data for each project based on the id number in the master sheet. I have created a report that will pull all of the projects assigned to me, so that I can update the weekly status column…
-
Help with Calculating Curriculum Average considering multiple criteria
Goal = calculate the total Curriculum Average % (for respective Regions 1, 3, 4 & 6) by combining the available quiz scores (in green) from all COURSES 2002-2008 (in the "Course" column). I want to filter out any rows that have blanks, 0% and Registered, aren't green (all non-green rows basically show the teammate hasn't…
