-
Join Sub-Parent with Children rows
I have a sheet that has multiple parent rows and I want to join a specific Parent Row to the children rows. When I use the formula =JOIN(ANCESTORS([High-Level Workflow Task]@row), "-") it includes all the parent rows above it like this "SM RDG LO-SCO Migration-FY2023 Budget & Scope-Gadget Group X: Thin Slice#1 @5%-Batch…
-
Linking contacts from sheet A to sheet B
I am trying to connect contacts from Sheet A to a workflow automated message from a form in Sheet B. Is something like this possible? Sheet A is a database of people listing names, skills, locations, emails, etc. Sheet B is fed by a form, I would like new rows to trigger a workflow to send an automatic message to a subset…
-
Formula column to autofill in a sheet populated by a form
Hello, I'm an avid Excel user but new to SS; I'm hoping this is doable. I have a sheet that is populated by a Form. I've added a new column to that Sheet to display Month/Year from the Date column Formula is =Month([Date]@row)+"/"+Right(Year([Date])@row),2) I'd like for this formula to auto fill as rows are entered from…
-
I have a formula that suddenly stopped working.
This formula has been in use for many months and has suddenly stopped working, and I'm getting an #INVALID OPERATION error. =SUMIFS([Annual Revenue]:[Annual Revenue], [Project Type]:[Project Type], "Custom Courseware Solution", [Revenue Type]:[Revenue Type], "Delivered") There are no formulas in the referenced columns. The…
-
AVG COLLECT Formula using a contact column not working
=AVG((COLLECT({Time to Audit}, {Audit Date}, <=DATE(2022, 1, 31), {Auditor}, CONTAINS("Marta Routt", @cell)))) This formula works when I have the Auditor column as a drop down and not Contact List. Why would that be?
-
Trying to generate a list of people based on criteria
Hello! I am trying to figure out a way of generating a list for a certificate program through Smartsheet. The certificate program requires that participants attend 6 out of 9 webinars offered in a year. We ask registrants to register with their first name, last name, and email address. I am hoping to use the email…
-
Using multiple IF statements with INDEX/MATCH
I am using a value posted in a column of my sheet using other formulas; say that value = L4, L5, or L6 (levels of approvers), search a reference sheet for a specific related column and return the email for the person. I have a more basic function working with standard Index/Match function but adding in the element of if…
-
Count Networkdays to TODAY only when End Date not listed?
Hello, How can I calculate networkdays to TODAY only when the end date is not populated? The following formulas work, but I can't figure out how to stop the counting of networkdays when the review end date is populated. I need to set some alerts when projects enter review and are not completed and the alerts are based on…
-
WORDAY FORMULA
Hi Could anyone help me amend this formula to count working days rather than days please? =IF(AND([IMMUNISATION INCIDENT REPORT FORM SENT]@row > DATE(2020, 1, 1), [IRRF RECEIVED]@row = 0), [IMMUNISATION INCIDENT REPORT FORM SENT]@row + 10, IF(AND([IMMUNISATION INCIDENT REPORT FORM SENT]@row > DATE(2020, 1, 1), [IRRF…
-
If / And
I believe the formula I'm looking for is an if/and but I can't get the argument set quite right. I need the formula to output a number based on the AOR and the tier level. Here is what i have so far: =IF(AND(AOR@row = "X", [TIER LEVEL]@row = "I", 25, [TIER LEVEL]@row = "II", 55, [TIER LEVEL]@row = "III", 75)) Appreciate…
