-
Creating a Milestone Sheet (referencing project plan)
Hi All, I have created a project plan in Smartsheets and am tracking milestones using tags How do I create a separate sheet which only shows me milestones, its start/finish (i.e start=earliest start date with a particular tag, and end=latest end date with a particular tag), its % completion (based on the milestone tag) and…
-
Case sensitive COUNTIF
I'm trying to mark duplicates in a particular column per the discussion here: https://community.smartsheet.com/discussion/comment/319478#Comment_319478 My issue is, the column I'm checking has a mix of uppercase and lowercase letters that define unique IDs, and COUNTIF unfortunately, is case insensitive, so it is marking…
-
Help With Index Match (#Invalid value)
I am trying to return a Contact value Cell from a lookup table, in the Column "Assigned Contact Email" according to "Week * + Day*" and "Shift Assigned To" in that Row This is what I want it to look like. ^ This is the other sheet with the look up table. ^ This is the Formula I have used. ^ ^ For the formula, Red is the…
-
COUNTIFS and NOT Formula Creation
Hello, I have a table of data that looks like this: I am needing to create a formula that will count all of the "1" in each column, that do not contain "OPEN" in the name field. So far I have created this formula: =COUNTIFS(Sun3:Sun27, 1, Employees3:Employees27, NOT("OPEN")) Does anyone have an idea for a formula that will…
-
NEW! COUNTIF not blank....
I want to count how many columns have a date in the "SMD/IME Appointment Date" row and where the "Case Closure Date" cell in that column is blank: =COUNTIF(AND(NOT(ISBLANK([SMD / IME Appointment Date]))), ISBLANK([Case Closure Date], )) Attempt #2: =COUNTIF(AND(NOT(ISBLANK([SMD / IME Appointment Date]))), ISBLANK([Case…
-
Use Predecessors for a Shared Start Date and Extended End Date
I am working on a marketing project plan template, and some of our campaigns spend multiple days in market. Tracking the performance of these projects happens for the duration of the project plus the duration of an attribution window. Example: An email campaign launches on 4/1 and runs through 4/7, and any email leads…
-
Adding an Amount from a Prior Week
Hello, I am currently trying to get a sum from two values. One of the values is from the most recent Sunday, and the other is the Sunday prior to the most recent one. I need to find the difference of them for a calculation I am using in another column. I am not sure of how to call upon the prior Sunday's value. I do have…
-
Help with counting formulas and exclusions
Hi there, I'm trying to create a formula that would count all instances where a certain option is selected from a Multi-select drop-down and then it would subtract any instances if a certain category has been selected in another column. For example: This formula gives me 13 counts: =COUNTIFS([Curatorial…
-
editing across sheets for text consistency
Hi all, I'm not even sure what to ask for, other than help. I have three key sheets that contain many of the same fields. I've (finally) figured out the Index Match formula to pull matching field in from another sheet (there was much rejoicing!) but now am needing to be able to edit the info. Is there a way to do that…
-
Pre-populate select fields in a Form (sheet B) from an existing sheet (Sheet A)
Is it possible to prepopulate select data elements of a form from an existing sheet that contains the data. Example we have two sheets. Test Execution sheet (Sheet A) to manage and track testing and Defect Log (Sheet B) used to capture defects identified the Defect Log would have form associated with it. Within the Test…
