-
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…
-
If 2 Criteria Met On Second Sheet, Display ID Number from Second Sheet on First Sheet
I am trying to write a cross-sheet formula in sheet 1 (field name is Row ID) to display the Row ID from a second sheet if the second sheet Newest field and ED field are both checked (see below). I've been looking through posts and attempting INDEX/MATCH, nested IFs, etc. and nothing is working so far. Any help would be…
-
Trying to incorporate ISBLANK into a formula
=IF(ISBLANK([Monday Job]@row, ""), INDEX(COLLECT({Per Diem Database Range 1}, {Per Diem Database Range 2}, [Employee ID]@row, {Per Diem Database Range 3}, [Monday Job]@row), 1),) Getting an unparseable on this one any help would be greatly appreciated. Brandon
-
Include Parent Row text in JOIN formula
Hello, first time posting, long time smartsheet users. I am trying to include the parent row text in my formula but not sure how. My Formula will only pull the text of the CHILDREN but not the parent. I need column "X" to be "BI, Engineering" =JOIN(DISTINCT(DESCENDANTS([Data Group]@row)), ", ")
-
Hiding Or Removing Duplicates In a Report
Hi all! I have a report on a sheet that has many duplicate values, and for the sake of nice presentation I would like them hidden. To further explain my situation, I have a sheet I am referencing that has tasks, days to complete, the week of completion of these tasks, and finally the avg days to complete a task per week.…
-
Convert drop down in text format to date format
I have seen various other questions in the community, but I can't quite get my equation there, I am trying to convert a drop down from a form to a date in the adjacent column. All dropdown items are formatted as follows: 7/07/20 Here is what I am trying: =DATE(VALUE("20" + RIGHT([Testing Date]@row, 6, 7)),…
-
Formula won't recognize cell data
Hi, I have several formulas in sheet summary that reference a single person in an assignment column in my sheet. The formulas are repeated multiple times and work just fine, however I seem to have issues with this single person. Any formula that includes this person, does not work. Any insight on how to fix this? I've…
-
I am trying to find the right formula referencing dates and an identifier column
I am working on a formula to check all boxes in the rows that match the identifier column if there is a projected date and it is greater than or equal to today. I collect the start and end date and if a class is delayed or ahead of schedule it collects the projected end date. What I want is if the class is still going on…
-
Formula, checking a Box with multiple criteria
Hello everyone, I have a question. I am trying to create a formula which is supposed to check a box if a specific word (lets say word1) is in column 1 and there also is a date in column 2 or if there is one of two words (lets say word2 and word3) in column 1 and a date in colum3. So far I have this: =IF(AND([column1@row =…
-
Populating the Parent Row from Another Sheet
I have a spreadsheet that contains all of the inventory information for a lot of products. These products are broken down into categories. For example, shoes (a parent row) would have the child rows of sneakers, heels, slippers. I am receiving orders from products onto another spreadsheet via a form. From the form, I am…