-
Count the number of non blank child cells
Hi! The sheet contains the following hierarchy: There are formulas (COUNT(CHILDREN())) in the blue cells of the Missing study column. I need to count the total number of white (child) cells. In this particular case [10] should appear. What the formula should look like? Thank you in advance!
-
Using a Database to Autofill Cells
Hi Smartsheet Community! I am trying to create a cell autofill for email and phone in one sheet using another sheet as a database. The issue I'm running into is how do you select between identical names with different email/phone. In the database sheet, I've created a duplicate checkbox column with a formula that will…
-
Cross-sheet reference limited to under 50,000 references
Excited about the increased cross-sheet reference limit to 100,000, I jumped on the bandwagon and started to replaced the Destination sheet LEFT(SUBSTITUTE(JOIN formulas with the Index/Match formulas using cross-sheet references. In this workflow, we are pulling data from 5 Source sheets into one “Central (destination)…
-
Formula to check a box if it meets multiple criteria in another sheet
Hi! I have two sheets: Sheet A: ID#, Date, and a checkbox Sheet B: ID#, Date Can someone please tell me what should the formula be if I want to Sheet A checkbox to check if Sheet A's ID# and Date match with Sheet B's ID# and Date. I tried a few different formula and it seems like it doesn't work: =IF(AND(MATCH([ID #]@row,…
-
Count how many "On Hold" projects for the Sheet Summary
I have a sheet set up with a (Multi Select) dropdown, and a complete check box, & would like to get a count of how many projects that are NOT complete & are On Hold. COLUMN: Info Still Needed - is a multi select dropdown COLUMN: Complete - is a check box I have tried several formulas with no luck.…
-
How do I set several cell values to a specific value in a different column
Hi, I am running into issue and I cannot think about a way to solve it. I have a column (Drop-down list) containing specific statuses (Status Column) which I need to match to 5 different report statuses (Not Started, In Flight, On Hold, Cancelled, Completed). The match is as follow: I can match the "Not Started" and "In…
-
RAG status based on dates overdue
Hi, I'm attempting to get an output of either, "On Track", "Overdue" or "Critically Overdue" for baseline milestone dates in Smartsheet, with the Overdue being 5 days over & Critically overdue being 20 days over respectively. I have drafted the initial IF statement, however i'm suck on how to add the additional "Critically…
-
Date Overlapping Formula with Same Resources
Hi Smarties, I am working on a challenge below hoping for some help. I would like to raise a flag (on a flag column), if Start Date and End Date of a project conflict with other Start Date and End Date. Projects are the ones with Header "0" Only if the Start Date and End Date of the projects are assigned to the same…
-
Cell History Changing Due to Time Zone for Column Formula?
Hi, I have a column level formula and the oddest thing is happening. My cell value keeps changing to a swapping date when the actual cell containing the date has not changed (cell history checked). I'm hunching that a user is using different timezone date format which is why the dates flip between MM/DD and DD/MM and back…
-
Forms or Formulas with appointment scheduling
Hello All! hope you can assist. Trying to have a form (external users fill out) to book a time slot - Once that time is gone - no longer available to anyone else and thus is removed from the options. Is there a solution, formula or best way to achieve this without using a third party booking tool? 🤔 Thanks Trish😀