-
Cost formula with a multiple select drop down column.
Hello, I am having trouble with a cost formula. I am trying to sum the total cost of a certain department. The department is in a multiple select drop down column. They formula will only sum the departments with 1 dept selected and will not pick up the cost if the column has multiple departments. See screenshot below. Fire…
-
How to Pre-Populate a Field in a Form?
Hello Smartsheet Community, I am trying to pre-populate my Work Order ID field in a form with the number it is assigned in its row. A while back, we were given a complex formula to include specific fields but it is very complex and hard to understand. Can someone please let me know how to add the Work Order ID to the form…
-
Date Expected Error or decimal return
I'm trying to return a date value based on the week number identified. So if I have "Week One" in my training date number, I want the Date column to return 10/17/22. I'm trying to use an IF formula to do this but keep getting #DATEEXPECTED error. When I try to convert the column type to text/number instead, it gives me a…
-
Isolating all text to the right of a character (e.g. "-") in a string
Hello Smart People - I'm parsing a string using helper columns. I want to return all text to the right of my last "-". For example, QA-IS-OPS would return OPS, and QA-ARC-RM would return RM. Is there a simple way to do this? Thank you!
-
How to exclude cells with formulas when using SUM formula for column totals
I'm wanting to use a formula to get the total $ amount for 2 columns in a sheet. The problem is, both columns contain cells w/ SUM formulas. I do not want to use or include these existing formulas in my new formula. There are 4 cells with formulas I want to exclude and some cells with text. Is it possible to get the total…
-
Count how many cells are between two values
So, the first one was pretty easy =COUNTIF({Export Range 5}, <30) - that works But, now I need to count the number of cells between 31 & 120; I just can't seem to make it work. =COUNTIF({Export Range 5}, >30 and <120) just gets me "unparseable". =COUNTIF({Export Range 5}, >30, <120) gets me "incorrect argument set". What…
-
Setting up VLOOKUP correctly
Hello, I have a source sheet that has a unique ID, and I use that as a reference to generate a number of references in a second sheet for extra details. I am using VLOOKUP to bring about 10 columns from the source sheet into the second sheet. The typical formula looks like this: =VLOOKUP([AOP ID]@row, {2023 AOP Entry Range…
-
Overlapping Dates and Auto-Flagging
Hello, I have created a communications plan for my organization. I would am trying to auto-flag (in the Overlap row) any communication entry that has overlapping Start/End Dates of the same Delivery Method. I am using the formula below, but receive the #unparseable error. What am I doing wrong? =IF(COUNTIFS(Delivery…
-
Turn Rolling 12 Month report into Rolling 24 Month Report
Using help from a discussion on Smartsheet Community, I have managed to create a rolling 12 month report, using the following formulas Month: =IF(MONTH(TODAY()) - [M1]$1 < 1, MONTH(TODAY()) + (12 - [M1]$1), MONTH(TODAY()) - [M1]$1) Year: =IF(MONTH(TODAY()) - [M1]$1 < 1, YEAR(TODAY()) - 1, YEAR(TODAY())) However, if I…
-
Time Tracking Formula
I need to track the hours of our paid on call fireman. I wanted to be able to create a form that asks the Incident number, date, time in, time out, and total time. For each incident, I selected the members that were on the call. On another sheet? I have each member listed out and a breakdown of the total hours for each…
