-
IF Formula with multiple layers
Hi community, I'm looking for help on creating a multi-layered IF formula for the following guidelines. Anyone an expert that can help me build it out? IF amount is > $100K Then this should be 4 IF amount is <$100K - $125K Then this should be 6 IF amount is <$125K - $150K Then this should be 8 IF amount is <$150K - $175K…
-
Index Match/ V look up or Data Mesh?
Hi, I'm using Smartsheets to input all associated software release notes across many different software versions (each software version has a separate Smartsheet). My issue is that sometimes certain tasks IDs appear in multiple versions of software. e.g. Task ID: 1234 may appear in software version V10 and V11. I want to…
-
Trying to Figure Out a SUMIFS Formula
I am trying to create a formula that add the 'total pieces of media' if the 'type of media' is 'still' and the 'current / upcoming' is empty. The formula I am using is below and I keep getting invalid operation. Not sure what I am doing wrong. Thank you for any help I can get. :) =SUMIFS([Total Pieces of Media]:[Total…
-
Need Help with NOT CONTAINS
Hello, I need help with a formula using NOT CONTAINS This is my current formula, which is giving me an incorrect count. =COUNTIFS({Another Sheet Column 1}, CONTAINS("X", @cell), {Another Sheet Column 2}, "1", {Another Sheet Column 1}, NOT(CONTAINS("B", @cell)), {Another Sheet Column1} NOT(CONTAINS("C", @cell))) Values of…
-
What formula to use to add $ and comma in the field
I have a "run-rate" field that sums up values from 4 other cells. What formula can I add so the summed value automatically has the $ sign and comma between the numbers? Also, can I use the same formula in the other 4 cells so that people don't have to add $ and comma when inputting data? Thank you
-
Need some help with a health formula for task risk level analysis
Hello, I am currently working on a formula for the health column in my smartsheet that is based on the the task status and the end date of that particular task. This is what I have so far but what I need is for when the "end date" cell is blank I would like for the health of that task to be blue. The reason is we do not…
-
Is there a way to change a status cell based on multiple date criteria?
I am looking for help get the below equation to behave like I want. Currently it gives me #INVALID OPERATION, which I have trouble shot and determined it is giving me this because it is trying to perform logical operations on null values. The formula works if I populate all of the below date columns, but that isn’t how the…
-
External data written to a sheet, updated hourly, breaks my model when updated. Help?
I have 8 or so files that are updated hourly from our ERP that live in a folder within a workspace. Things like inventory, PO, and other data, that I am then referencing thru lookups or sumif's from other pages to manage an aspect of our business. Everything works fine, until the files are overwritten as part of the hourly…
-
What formulas would determine active proof, current task, and next task?
In my sheet summary, I would like to indicate 1) which proof is currently active, 2) the task that currently needs completed, and 3) the next task to complete once the current task is completed. *Note - The tasks to be referenced will be one of Staff Submissions (children not needed), Consolidate Submissions, Send Proof to…
-
Date Formula that works by Month
I stoped by the formula booth at #Engage2022 and worked with some super helpful folks to think through a solution to show a date 6 months before a date in another column. It’s super complicated! Would love a formula update that automatically recognized and could transform a date adding or subtracting by months rather than…