Formulas and Functions

Archives for April 2022

Pulling Column Info Based on Column Containing Data in Separate Sheet
I am trying to figure out how to pull in the Name, County and Status from a master list onto a new sheet based on the master's Status column containing data. I am wanting to do this so that we are not overlooking anyone. I have been trying to figure it out and have seen that I probably need to use the INDEX and COLLECT…
Formula to assign a contact on all rows of main parent cell
Hello! I want to assign all rows under the main parent cell (has multiple sub parent cells within) the contact that we inputted into the main parent row. When tasks are added within the project, I want it to auto assign that row. Can anyone help me with a formula for this? Thanks!
Count based on two criteria
Hi, This is just an example below. I am trying to get a formula for the 'count' column. I want to return a number from 'dates' and 'group'. If there are two of the same dates with the same group, I want a '2' in that cell. If there is only one date for that group, I want a '1' in that cell. Does that make sense? TIA
Formula to convert date (e.g., 03/13/22) to text/number field (03/13/22) in same sheet?
A sheet that has a Dynamic View includes a date field (Start Date) that Dynamic Viewers need to use as a filter. Since users cannot filter by date fields in Dynamic View, I've added a helper column (text/number) in the sheet but cannot find a formula to pull the dates in the Start Date column into the helper column so…
If Formula Unparsable
Would anyone be able to help see what's wrong with this formula? I'm not sure how I'm screwing this up.. My formula works like this but if the status is anything other than partial I'd like the cell to say "N/A" =IF(Status@row = "Partial", QTY@row) - [Received Quantity]@row So I just tried this: =IF(Status@row = "Partial",…
What formula can I use to identify blank columns?
I'd like my column to call out "missing fields" if someone fails to input information to all columns of the spreadsheet. Here is the formula I have in place but I am getting an error. =IF(ISBLANK([Partner Name]1):([Tracking Method]1), "Missing Field")
Fix my Formula
Can someone please tell me what I'm missing here? I'm trying to count the number of records where "Stage 1" finish date is in the past and "Stage 2" finish date is in the future. I'm not getting any syntax errors and each piece of this formula works on it's own, so I must be missing something in the connection between the…
JOIN function for child row
Hi everyone, I'm currently working on a way to leverage formulas and row hierarches to create a ROW ID column. Essentially what I would like to happen for children rows to have the same "item ID" as their ancestorial row. This is what I have so far for the parent and child row formulas (currently seperate) Parent…
Can you lock comments so that those with certain permissions cannot see them?
I know you can lock rows and columns, but can you lock certain comments so that those with certain access levels cannot see what is in the comments? For example, we are giving view only access to certain people on a stakeholder list, but we do not want them to be able to see the comments we have for certain rows. Thanks!
Trying to apply conditional assignments to a column based on either a formula or VLOOKUP
There must be something I'm missing. t seems like either of the following approaches should work but don't. I'm trying to auto assign a value within a specified column ("Power User") based on the values captured in two other columns ("Process Area" and "System") within the same row. Here's a screenshot of the validation…

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

Help creating formula to update inventory automatically
The following is our brochure requests sheet. Right now we manually update our inventory each time we receive a request. Here is a snapshot of the new inventory sheet. I'd like to create a formula that automatically subtracts 100 from Spanish Qty each time the Request Completed is checked, the Request Denied column is…
If Function - Nested - Negative Percentage - Symbols
I have a formula that is driving me insane. I am trying to get symbols to change color based on the % to Plan column (see screenshot). If the % is positive, I want the On Track Column to show Green. If it is negative BUT greater than -25%, I would like to show Yellow and all others to show Red. The problem is I am getting…
If Date is blank + past formula
I can't figure out why this would return a "yes" when the date cell is blank. =IF([Date of Site Survey (Product)]@row = " ", " ", IF(TODAY() > [Date of Site Survey (Product)]@row, "yes", " ")) Intended logic/result - If date cell is blank, then "blank" (this worked when I tried without the 2nd argument added) If date is in…

Formulas and Functions

Discussion List

Help Article Resources