-
How do I set an automation without a trigger or is there a workaround to create a trigger
Hello! I am needing to set an automation to lock and unlock rows. I have the automation set to lock the row when it is changed from “open” to any other value. I need a time based function that will allow the cell to unlock a certain time period after it is locked by the first automation. Essentially I want to ensure that…
-
Is there a formula in smartsheet that would create a 2 way link between cells?
Hello, I have multiple sheets that I would like to link all into one Master Sheet. I want the cells to have a two linking methods between each other. If a cell changes in the Master Sheet, I want it to reflect to the other sheets and if the other sheet decides to change the cells, it would reflect back to the Master Sheet.…
-
Needing Help with an IF TODAY formula
After reading countless articles and trying countless combos, I still can't get this formula to work. I am trying to give a 30 day notice to an expiration day with a status column. =IF([TIPS Expiration]-TODAY()>30,"Green", IF[TIPS Expiration]-TODAY()<30,"Yellow"))
-
Budget v. Invoice Cost Codes
I have two Smartsheets. Sheet 1 is my budget broken down into various cost codes. Sheet 2 is a log of all my invoices with cost codes and amounts assigned to each invoice. I am trying to take the amount from each invoice and have them populate in my budget according to their cost codes. The budget amounts for each cost…
-
how to calculate how many staff are on site each hour?
Because of Covid, we need to know the max number of staff that are at the worksite each hour. When I had a "fixed" time frame for everyone, the formula was easy and made sense. Example, when everyone had 8:00 to 10:00 shifts and 10:00 to 12:00 shifts. But now I have random shifts and I don't know what formula I can use to…
-
Error w/ Formula (#INVALID)
Hi Formula Friends - Can you help me identify what's going wrong with the following formula (Reference Cell: 'Count1'): =COUNTIFS([Work Related Exp]:[Work Related Exp], 1, Created:Created, AND(DATEONLY(@cell) >= [Encounter Date]$2, DATEONLY(@cell) <= [Date Resulted]$2)) I was working a few days ago; however, something…
-
Email / Name reference formula help
Hi, I am trying to create a formula where an entered email address automatically populates the person's name associated with that email in an adjacent column (see below). The data is referenced from a master sheet of people's email/name contact information. I have this function in a different sheet and it works perfectly…
-
Display partial content from a cell
Hello! I am attempting to show part of one cell in another cell. This is the formula I tried but I am getting inconsistent results. I am trying to just show everything after the - in the Author ID column. Where am I going wrong? =IFERROR(RIGHT(Contributor@row, FIND("-", Contributor@row) - 1), "")
-
If/countif returns incorrect arguement
So I'm using the following formula and getting errors. =IF(COUNTIF([ID Dump]:[ID]), "CURRENT", "NEEDS") Basically, I'm trying to get a return of CURRENT if any of the values in the "ID DUMP" column are in the "ID" column, and then a NEEDS if the number in the "ID DUMP" is missing. I used the following formula in excel and…
-
Trouble with COLLECT in INDEX/MATCH
I'm trying to troubleshoot an INDEX/MATCH formula that contains a COLLECT statement which is supposed to limit what cells are being used for the MATCH. I am referencing a project schedule, looking for the "next deliverable" - we have a column called "Function" in which the schedule tasks can be defined as such. (See…