-
Formula help with Index Match and Contains
Here is what i have in 2 ways but neither work: IF(CONTAINS("X1", [Model Type]@row), INDEX({Other Sheet Range}, MATCH([Email 2]@row, {Other Sheet Email Range}, 0)), "") IF(CONTAINS(INDEX({model}, MATCH([Email]@row, {assignee}, 0)), "X1"), INDEX({serial number}, MATCH([Email]@row, {assignee}, 0)), "") My goal is to retrieve…
-
Join Collect formula that excludes the current row from the results
I'm trying to create a formula that creates a list of duplicates in my sheets, I've gotten this formula to work but it's showing the original row within the list of duplicates. I tried adding an auto number column and including a condition that the list shouldn't include the current row. However, the addition of this to…
-
CountIFS with multiple criteria in second range
Looking to capture the certain status of a Purchase Order in a list of orders associated with several different projects. So i have range 1, criteria 1 = "Red", Range 2, Criteria 2 = Multiple project numbers. Keeps returning unparseable or incorrect arg. Is there another formula I should be using?
-
Lock or Store Date/Value Solution without using Zapier
Hi all, I hope you're well and safe! I've developed a solution that lets you store the date or value. You'd use the copy-row automation and a VLOOKUP or combination of INDEX/MATCH to make it work. We'd trigger the copy-row to another sheet and get the created date/other value and then use the VLOOKUP/INDEX/MATCH to get it…
-
Formula (if this is and date beyond, check flag)
Help with this formula (generated through AI). I need it to look at this cell (if it is blank) and another cell (date) is greater than 3 months, turn the FLAG on in ____ (cell). I am missing this other cell name, I believe. Cell/Field is called Protocol FLAG. =IF(ISBLANK([DRAFT Protocol Received (Date)]@row) AND…
-
[Formula Help] - Countif department and not blank?
Hi everyone - I'm stuck trying to figure out how to format this question better. I'm working on building a dashboard for some reporting. The dashboard captures metrics from different quarters. (Average number of training hours). I'm trying to figure out how to set up a formula so that empty cells aren't counted as part of…
-
Index Match formula = unparseable
Very new user to Smartsheet. Two questions: What formula change needs made to not get error? Would it be better to do index and collect? Sheet: Fields Appointments: Appt #, Date, Time, MRN, LLL Language, MRN Language lookup (field populating) MRN Additional Information: MRN Master, MRN Language, Eff date, Last updated…
-
Record a date of last attached file
Hi all, I have a main sheet that gets updated every time we manually attach a new file to it. I have a number of reports and dashboards that automatically update upon this manual attachment. I would like to record the date upon which the main sheet was updated specifically via attachment of this file (we update the sheet…
-
How Do I add Sum(Children) at the bottom of the children rows
Hi, How do I use the =Sum(Children) function in Smartsheet to sum child rows and display the total at the bottom. I usually create a parent row, then add rows beneath it, indent them and add the formula =Sum(Children) to the parent row to calculate the total. However, that structure was confusing for users, and they…
-
Multiple 'If/Contains' Formula
Hi! I am compiling a list of IP addresses that are associated with phyical sites. I am able to create a basic IF/Contains formula to populate the 'Location' column based on the 'IPv4' row. Issue: The issue I am running into is that I have many locations. If i keep the list small (20/30 locations), the formula works…