-
Auto copy from one column to another, but only IF...
I'm looking for a formula to copy information from one drop-down column to another automatically, but only if a third column contains a certain word. Is this possible? For example: Column 1 could say say "Title" or "something else" Columns 2 and 3 have a drop down menu of "In progress" and "complete" If Column 1 says…
-
The dreaded #invalid data type
Hi guys, I'm progressing with my basic formula however what do i need to add into the below formula as an example so i don't get the unsightly #invalid data type in a cell just because the start date is blank? =IF(MONTH([Start Date]@row) = 1, "January", IF(MONTH([Start Date]@row) = 2, "February", IF(MONTH([Start Date]@row)…
-
How to create a formula where if multiple criteria is met, a checkbox is marked?
Hello, I am pretty new to using smartsheet formulas, so bear with me! I am trying to create a formula where if multiple cells contain certain text, a checkbox in another cell will be checked off. I want the checkbox to only be marked if multiple criteria are exactly met. If all criteria is not met, I would like it to…
-
Using Index Match for Multiple Value Dropdowns
Hello, I'm trying to utilize index matching for some metadata sheets and using the formula I am currently utilizing, the data appears blank. The formula currently being used is: =IFERROR(INDEX({Portfolio Summary Data}, MATCH($[Project ID]@row, {Project ID}, 0), MATCH([Impact/Benefits]$1, {Portfolio Summary Header Row},…
-
Formula to check box if Date is 7 days ago
Hello all, I have an idea to help me initiate an update request to add a new row. I'm thinking about adding a helper column with a checkbox and a column formula to check the box if the Date in the previous row is 7 days earlier than today. Do you think that will work and if so, can you help me with the formula? Mine is not…
-
Formula to Sum all Selections with a Number
Hello, I'm looking for a formula that can SUM all dropdown selections that contain a number. For example, One Cell Contains: Advisor_1 Instructor_2 TA_1 Is there a formula that could sum all of these selections so that the returned value would be 4? The column is a Multi-Select Dropdown
-
calculate quarter end for corporate fiscal year
Hello! I would like to calculate quarters based on our fiscal calendar, which starts in November. I wrote the following formula to determine quarter and year. It works for all quarters except for Q1. For dates in Q1, the formula is just returning "Q1" and not the year. Any assistance would be greatly appreciated. ="Q" +…
-
Formula for counting cells with a specific date
The sheet is set up to fill in the create date for a ticket logged from a form. I need to count how many were opened each day and used the below formula for each date but the count says zero when there are line items for that date. =COUNTIF({TJ Regional Health eService Range 6}, "08/01/22") When I hover over the date in…
-
IF Statement between Ranges
Hello, I am not sure what i am doing wrong in the formula =IF([Current Risk Score]@row < 15, "36 Months", IF([Current Risk Score]@row > 70, "3 Months", IF(AND([Current Risk Score]@row > 16, [Current Risk Score]@row < 29, "12 Months", IF(AND([Current Risk Score]@row > 30, [Current Risk Score]@row < 50, "6 Months"))))))…
-
Countif fo rmultiple Criteria
I keep getting an unparseable error. I have tried the below four formulas which include referencing another page. =COUNTIFS({TJ Regional Health eService Range 3}, "Open") + COUNTIF({TJ Regional Health eService Range 3},"Client Assigned") + COUNTIF({TJ Regional Health eService Range 3},"Cerner Assigned"), +COUNTIF({TJ…
