-
Formula to reference one column based on entries from another column.
I need help on a formula for Project Assignment when a dollar amount is what determines who gets a Project assignment. For example, A Facilities coordinator will get a project if it falls between $5k - $24,999 Project Manager A will get a project if it falls between $25k and $99,999 Project Manager B will get a project if…
-
What is the issue with my formula?
=IF(AND(% Complete]3 < 1,[End Date]3 < TODAY()), "On Time", IF(AND([% Complete]3 < 1,[End Date]3 = TODAY()), "Due", IF(AND([% Complete]3 < 1,[End Date]3 > TODAY()), "Past Due","Complete"))) If i validate each part of the formula separately it returns my intended value however when I try to embed using IF AND, it is always…
-
COUNTIFS criteria including dates after X
I'm trying to create a formula that counts the number of entries submitted to a separate sheet per person completed since a specific date (3/24/2020). My formula that works for counting all entries by individual is =COUNTIF({Other Sheet Range1}, [Member Name]1) but when I try adding the AND {Other Sheet Range2}…
-
Auto Generated Number Referencing
Hi, I noticed when using Index match on auto generated numbers something weird happens. On sheet A I use Index({desired result Sheet B}, match(sheet A @row, {search Criteria Sheet B},0),1) where Sheet A @Row is an auto generated number and search criteria sheet B is a text/number input. I found you have to use…
-
Count Formula
Hello! I am tracking assignments and a person can have more than one assignment. I need to be able to count the number of persons assigned on the sheet but don't want to count dupes. The fields are "First Name" and "Last Name". I'm trying to use the =COUNT(DISTINCT(RANGE) formula but I'm not having any success. Also, there…
-
what is the size limit for a file attachment via API
I have build a webpage that uploads attachments to a row in smartsheet. It works fine for 12 MB file. But I have been trying to upload a file which is 100MB via API to a row, but everytime I get below error: {"response": {"statusCode": 500, "reason": "Internal Server Error", "content": "<< text/html;charset=UTF-8 content…
-
Forms duration stamp
Is there a way to add a START and END timestamp to the Form/Sheet that will enable me to determine the duration it has taken for each respondent to complete the form?
-
Working Days Configuration for an Organization
Hello, Working days and non-working days are configurable on sheet-level and account-level, but I wonder whether we can configure that on the organization-level. I want to set the default non-working days and public holidays for my organization then let my team members edit their own paid holidays on their accounts. How…
-
Best practices when dealing with large amounts of reports
Good afternoon everyone, I was curious to know what everyone's best practices are when it comes to managing a large number of reports and how to implement holistic changes? We have dashboards designed for multiple locations and in those dashboards are reports that are filtering content from a sheet, filtered to those…
-
VLOOKUP with Duplicate Data
I have a master sheet that may have duplicated column data, like: Client Name | Status (dropdown) | Delivery Date | Feature (checkbox) Client A | Rescheduled | 2/5/2020 | 0 Client B | Postponed | 2/12/2020 | 1 Client A | Confirmed | 3/17/2020 | 1 Client C | Requested | | 0 I have a sheet I'm using as a template to create…