-
Ways to avoid 25000000 formula cell reference limit
We have a sheet that we use for time tracking for some of our projects. We have about a dozen people supporting projects and they may log their time multiple times a day. I have a SUMIFS formula that adds the duration for each client project. Formula: =SUMIFS(Duration:Duration, [Client ID]:[Client ID], [Client ID]@row ) I…
-
Have Header Row Sum All Items Within Same Column
I've been trying a few different things to see if I can get this to work and unfortunately I'm having a hard time. Right now, I have a resourcing sheet that has header rows for each team member and each week has it's own column. Every task needs to have the number of hours entered per week for the work effort. Because of…
-
Data Shuttle does not import times from excel correctly
Hello, We have an excel sheet that has a Start time and stop time for tracking how long items took. However, when we use the Data Shuttle, SmartSheet converts the time to a date. How can we keep this in a Time format ( 8:00 am)? This is a critical part of our playbook with clients, and we need Smartsheet to be able to…
-
Why do I get #invalid value?
Hi everyone! I am using this formula in the Date column to pull a date if 2 criteria are met (name of the client and the specific module): =INDEX({Date}, MATCH([Column11]3, {Client}, 0), MATCH([Column4]@row, {Module}, 0)) It works in the "Module 1" row, but not below, any idea why? Thanks!
-
Pull date from text string for conditional formatting
I have a text string column that always ends with "by [date]" on a sheet that someone else manages. They want to keep the text in that paragraph instead of using a separate date column, but they want conditional formatting on the date column… I have a formula that pulls the text value from the Next Deliverable(s) column:…
-
Multiple criteria for Index/Match (or collect?)
Hello, I am trying to return data from a cell in a reference sheet based on (2) criteria - one at the row, and one in the 1st row of that column. I have metrics sheets that accomplish a similar function but those are using sumif for calculations, whereas here I just need to pull in text. How do I create an index/match or…
-
Automatocally changing due dates and status on recurring tasks (Monthly, weekly, etc)
How do I make a row update the due date for a monthly, weekly, and/or quarterly recurring task? I would like it to automatically change the due date to one month, one week, or three months in the future and change the status back to Not Started once a recurring task is marked Complete. I need to be able to do this at the…
-
IF and AND for driving progress bar Symbol
For progress column, I want to use the symbol column with bar option (Empty, Quarter, Half, Three Quarter, Full) and it should be driven by task % Complete. if % Complete Value is 0% then progress should be Empty, if complete value is between 0 - 35% then progress should be Quarter, if value is between 36-65% then progress…
-
Multiple Emails in Contact List
I have a contact sheet and a main sheet. There are instances when I have 2-3 individuals that need to be notified based on the information on the main sheet. I created this formula to pull multiple emails from the contact list: =JOIN(COLLECT({Department Chairs}, {CourseName}, CONTAINS([APA Courses]@row , @cell )),…
-
Project Schedule Question
I'm looking to make adjustments to my current project schedule. Currently I have an asset due date milestone, a list of tasks with durations and predecessors and a go live date milestone. I am able to put in the partners desired go live date and the formula deducts 6 weeks to determine the asset due date. We are making it…