-
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…
-
Help with SUMIFS
I am designing a budget sheet and want to Sum a series of cells if they meet 2 conditions. 1st I want it to check an expense category, ie. groceries. I then want it to check that it falls within a specific date range. Below is an example of the formula i have built and it works: =SUMIFS({Expense Collector Range 4},…
-
Formula Help Extracting Month Only from Formula Driven Column
Hi all- I need help trying to extract the month number from another column. My 'Notification Date' column uses a formula (based on the service 'Type') to calculate a specific timeframe. I need to extract only the month from this 'Notification Date' and populate it in the 'Annual Review Month #' column. I was good until I…