-
Formula that will look at how many submissions there have been by group in the last 7 days
I am trying to figure out how to pull the data from a different sheet to build my roll up and dashboard. I am trying to use the following formula. The goal is to be able to show how many submissions there were last week for each group. =COUNTIFS({JHA Tracker with Form Range 66}, @cell <=TODAY()), ({JHA Tracker with Form…
-
Snapshot of Smartsheet comparing TODAY to Start and End Dates
I have a grid sheet full of contracts: each row is a contract. Each row has a START DATE and END DATE along with other details about the contract. I was tasked with creating a dashboard to represent this grid sheet over time. I am now creating a reference sheet to pull "snap-shots" of data from the grid sheet. I put in a…
-
Add the ability to enter simple formulas into cells in a Report View
I've got a Report View that simplifies what columns & rows are displayed out of the full sheet so that a user can input data on a weekly basis. We recently are facing a scenario where we need to sum up multiple values into a single cell. Although the math isn't 'hard' it is more efficient to simply type in i.e., '=123+456'…
-
Index/Match vs Index/Collect vs Index/Contains
Creating a sheet to collect and answer FAQs, I am attempting to populate the System/Tech Supporting SME dependent upon the selected system/technology. I have my primary collection sheet and then I have made a reference sheet for some formulas. Relevant columns with fake data from the primary collection sheet. System/Tech…
-
Retrieve value from "Top Parent" when Ancestor level = 2
I have a project management sheet. CSYYXXXX is the header for the project name column Tasks is the tasks column - at the parent row, I just write "Tasks" and then there are child rows below that. Some of these child rows and another level of descendant rows. I want a column formula (to serve as a helper column for various…
-
Merged: Find and Replace Function in Formulas
This discussion has been merged.
-
Project Status Formula and Checklist items
We have a project checklist template that is provisioned via Control Center. There are hidden columns (the "Task Not Needed" referenced in the formula) that show only the checklist for the Project Type selected in intake - the tasks that are not supposed to appear are checked because all the project-specific checklists…
-
Help with cross cell referencing
I have a sheet of incidents that I want to be able to count the number of certain types in a month. In my incident sheet I have a column called "Type of Incident", that has values of Safety, Environment and Quality A column for the date, that I have used formulas in helper columns to get the Month and year. So now a cell…
-
Status Rollup to Parent Formula
Hi, I have this formula that works when automating parent/child tasks in a project plan. =IF(COUNT(CHILDREN(formula@row)) > 0, IF(COUNTIF(CHILDREN(formula@row), "Complete") = COUNT(CHILDREN(formula@row)), "Complete", IF(COUNTIF(CHILDREN(formula@row), "Not Started") = COUNT(CHILDREN(formula@row)), "Not Started", "In…
-
How can I use index match + either another index match or an IF statement to pull Data from multiple
How can I use index match + either another index match or an IF statement to pull Data from multiple sheets? In my case I have 3 sheets: Sheet 1 - Current Project List - consists information that track active projects. (each Project has a unique project code) Sheet 2- Closed Project List - consists of previous years…