-
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…
-
Index/Match/Contains formula not working in all cases
I have a question regarding an Index/Match formula. This is for a cross-sheet function. I have two sheets: Call them "Project" and "Jeopardy" Project sheet columns: PID: this is a 6 or 7 digit number identifying the Project Path to Green: this is the "Path to Green" for a Jeopardy against the project. This is where the…
-
How to Sum up Children Rows when another Column is Checked?
Hello all! I have been searching and trying for an answer most of the afternoon now, with no success. I have a budget sheet I am working on that needs to sum up the children Amount rows in a month when a checkbox in the Reflected in Account checkbox is checked. So far I have been trying this formula:…