-
How do I add months to a date without there being a month duration option?
I'll try to keep from getting too convoluted. On my main sheet I have a "Start Date" that pulls a date from another sheet. I have built a long checklist of items that are due based on this "Start Date". Each item has a different length of duration. There are a couple items at the end of the list that I was looking to use…
-
Formula to Assign Symbol based on a cell's value
This is a complicated one that I once got to work with RGY balls, but cannot figure out how to apply this to the Pain scale emojis. What I am trying to do: (Using the pain scale symbols) Have symbols automatically changed based on the value in the corresponding cell. I am already using a formula to assign a value based on…
-
Run a formula on a destination sheet when rows are moved to it
I'm migrating a manual process we have into Smartsheet, and keep finding new hills to climb! : ) Essentially the process will result in document generation (PDF) after different team members submit info onto a sheet using Forms. First, I found a formula solution that worked for me to combine all sheet data (from columns)…
-
Days to go formula
I have a dedicated column "days to go" I'm trying to get the formula to take the networkdays between today and the start date (accounting for dates in the "holiday helper" column. This formula works but gives a negative number. If the date is 5 days in the future I'd like it to read 5 not -5. If in the past be negative,…
-
Formula for COUNTIFS and Dynamic Date Range
Hi Smartsheet Community, I am really struggling to get the right formula and have tried so many now I am losing it! I am trying to prepare dashboards for weekly project meetings based on SLAs. I need to look at the count sitting in a particular status that have a Contract Date of >28 days, 14-28 days and <14 days. Because…
-
Index Formula with CountIFS Running Total
Hi - trying to make a Training Matrix for my company. "Matrix" sheet has the following columns: Row: (Auto-Number) Doc ID: (Text = document needing to be trained on) Employee 1: Check Box if they need to be trained Helper: column formula to give running count of checked boxes =COUNTIFS([Employee1]:[Employee1],…
-
Total Percentages Based off Check Box Selections
Good Morning Smartsheet Community, I am trying to calculate % based off check boxes selected on each row. Below are the fields I am calculating and the formula being utilized. Formula For %: =COUNT(Construction@row:Security@row, 1) / COUNT(Construction@row:Security@row) (With this formula I seem to be getting 117% with the…
-
Index/Match with cross sheet references
I have four sheets that I want to summarize in a separate sheet. If I am using an Index/Match formula, can I apply it to Child rows also? On my sheets, I have an amount columns, but some amount cells are sums of the two children rows beneath it. So, when I use the Index/Match formula, it only applies to the main Parent…
-
OR function referencing other sheets with Index - returning INVALID COLUMN VALUE
This is driving me nuts. Sheet1: {Sheet1_CC} = Course Code (Text/Number Column) {Sheet2_MRC} = Modify Replace Code (Text/Number Column) {Sheet1_AT} = A Task Dropdown list with restricted options (NEW, MODIFY, REMOVE) Basically a course code can be in either column and I need to pull relating task into Sheet2 Sheet2:…
-
How can I change this formula to exclude holidays?
I have set up a project status report to automate expected deadline dates for certain project deadlines. My current formula excludes the weekends, but not holidays. What can I change/add to this formula to do so? =IFERROR(WORKDAY([CD Kickoff (E)]@row, +1), "") Also, if I need to have the dates counted by 4 weeks vs days…