-
COUNTIFS in Sheet Summary utilizing a Contact Card column
I am trying to create a sheet summary, where the field is a formula that relies on a contact card within a column. However, it keeps returning a result of "#NO MATCH". The columns involved are, 1:) Priority (symbol column) for Up, Unchanged, and Down, and 2:) Contact Card column. I am trying to get a sheet summary formula…
-
Mark "True" If Value in Cell is Found in Column AND Between Two Dates
Hi all, I am trying to build out a formula that will return "True" if the CIF #@row can be found above it in the same column (CIF #) AND the Days Since Created is between 30 - 365 days. All of the formulas I have tried have only gotten me so far as the "AND". Adding in the dates screws it up. The goal is to determine if…
-
Best practice for cross-team project dependencies and consolidating reporting?
Hi everyone, I am looking for advice on the best way to architect a cross-team project tracking system. My current setup: I have multiple teams, each maintaining their own individual project sheet. Each sheet contains a list of projects specific to that team. The Goal: Cross-team Dependencies: I want to add a dropdown…
-
Sheet Summary - Column Totals - How to Combine in a Report
I have #'s that pull from each project into each sheet's - sheet summary. Those then get pulled into a sheet summary report. They are currently grouped by what channel they are in. Each column gives a total sum. I was asked to see if we can combine the total sum into one number, so it shows how many total SKU's are being…
-
Copy the most recent application cell data from another sheet
I have a master sheet with promotion applications linked to a SmartSheet form. Each applicant is identified by a ID number. There is a second sheet with the managers evaluation form. I don't want to send an update request or approval. I then want to copy the cells from the managers evaluation back in on the same row as the…
-
Exclude canceled tasks from the automatic calculation of the parent's % complete
Hello everyone, I'm working on a project sheet (with dependencies enabled and the Gantt view) and I'm looking for a way to exclude canceled tasks from the automatic calculation of the % complete on parent rows. My requirement : When a child task changes to “Canceled” status, I want it to no longer be included in the…
-
Counting and Position
I am looking identify a records position within a count. Meaning if I have a CountIf that comes back 10 records I would like to know if I am on the 1st record or the 5th record.
-
Using SMALL + COLLECT to rank items by due date — duplicates showing the same row
I'm building a "Top 4 Critical/High Priority" tracker on a dashboard sheet that pulls overdue items from a RAID Log using INDEX/MATCH/SMALL/COLLECT. Here's my current formula: =IFERROR(INDEX({Task Title Ref}, MATCH(SMALL(COLLECT({Due Date ref}, {Due Date ref}, <=TODAY(), {Priority Ref}, OR(@cell = "2-High", @cell =…
-
How to enlarge cell formula reference limit
I have a sheet with 5000 rows and 30 columns. with 4 columns of them are formular columns. How can i reach this limit for 25000000 formular cells reference? How can i enlarge this limit? I need those formular to link some data from another sheet as there is also Max cells limit for one sheet. How can I join those data…
-
Formula to Automatically Calculate Remaining Budget Based on Multiple Conditions
Hello Smartsheet Community, I'm trying to build a formula that automatically calculates the remaining budget for a project based on several conditions, and I'm running into issues with the logic. Scenario: Each row contains: Approved Budget Actual Cost Project Status (Not Started, In Progress, Completed) Department I want…