-
NEW! Your survival guide to formulas is here. Meet the Formula Handbook.
Hi Community, Whether you’re summing child rows, calculating budget minus actual, or doing cross-sheet data lookups with multiple criteria, formulas are a must for tailoring solutions to your needs. The new Formula Handbook is here to help formula beginners and pros alike. This template contains: A glossary of all…
-
How to calculate the first date of the previous month, including when the year changes
I had the following formula set up to calculate the first date of the previous month, and now that the year has changed the formula is no longer working as intended to pull in December data. How can I fix this so that it shows the date as 12/1/2025? Current formula: =DATE(YEAR([Date Column]10), MONTH([Date Column]10) - 1)…
-
Pulling from 2 different sheets into one sheet
I am trying to pull project phases from one Smartsheet and project checklist items from another sheet into one sheet. I would like the information from both sheets to be on the same line in the destination sheet. (Report puts them on two lines) Below is my current formula and I received the error #Incorrect Argument Set
-
Logic for parent row status
I have a to do list with parent tasks and children tasks. I have a status column with a dropdown menu with the options “Not started,” “In process,” and “Completed.” I want to have a formula that auto-populates the parent task with the status based on the children tasks. Here is the logic that I want the formula to use: If…
-
Countif - No Match
Cant figure out why this is not working. It comes back NO MATCH. I have changed it to true and to 1. Nothing works. HELP
-
Index/Match
Hello when matching between two smartsheets using this formula =INDEX({Livestock Transferred on Range 3}, MATCH(nlisid@row , {Livestock Transferred on Range 4})) it sometimes returns #NO MATCH even when there is the same exact data in nlisid to match to, they are both dropdowns. Out of 909 lines 59 are not working…
-
Using SUM with child rows
I have a parent row that I'm using to sum all child row values where Children = 0 and Status = "Not Started". This is my formula. =IF(OR(ISBLANK(Status@row ), Hierarchy@row = 0), "", IF(Hierarchy@row = 1, SUM(CHILDREN()), IF(AND(Children@row = 0, Status@row = "Not Started"), 1, 0))) However, if any of my task rows are…
-
Supervisor Based "Current User" Filtering in Reports
I’m hoping to get a second set of eyes from the community in case I’m missing something obvious or there’s a cleaner pattern I should be using. I’ve used ChatGPT to try and help me group/organize my situation coherently…. Because at this point I feel crazy… I’ve literally worked on this for hours. Environment •Smartsheet…
-
How to link to data in one sheet and transpose the data in a new shieet
I have a sheet that has data displayed both horizontal and vertical. The primary data is horizontal (date) with information below each date, 4 rows of data. After the data the dates for the next set of data is horizontal with information below it vertical. 1/5/26 1/12/26 1/19/26 1/26/26 JP OS TT LL OS RT RR SS That data…
-
Date & Time Stamp of Change
Hi, I am trying to get a column that provides the Date (easy with Record a Date) but I need the TIME as well. Most of my tables I just use the right function on the Modified Date because the only thing updating those tables are automations or data imports. But a few tables have automations, data imports, and manual inputs.…
-
help with auto numbering my projects that have children and grandchildren
I want to assign project numbers to the main project, and then append that project number with .(letter) for the Subprojects, with Tasks still being assigned to the main project number, with the letter of the Subproject, and adding more hierarchy, such as I'd like the task to read 40.c.1, or something similar that…