-
INDEX/MATCH ERRORS
I have used the index/match formula successfully , but in my current project, for some reason it isn't working. I have my two sheets (Open Adp) and (A/N Referrals). In Open Adp, there is a Case ID and then a person's name associated with that ID. I'm trying to have the person's name from Open Adp sheet be pulled over into…
-
How do you create a dynamic list of unique values from multiple sheets?
I am trying to create a "level of effort" calculation where we compare the count of bidded, lost, and awarded projects against a specific criteria, which is often a dropdown column in the source sheet. I have tried using JOIN, COLLECT, and INDEX from at least a dozen posts in the Community but nothing has worked to date.…
-
COUNTIFS Formula Based on Status and Due Date
I'm looking to create a formula that will count the number of overdue tasks. This is based on the "assigned to", "status" and "due date". My current formula is =COUNTIFS({[Sheet Name]}, <TODAY(), {[Sheet Name]}, ="[Assigned To]"). However, this is pulling tasks that are due in the past, but are completed.
-
How do I list all predecessors (direct and indirect) for a given row?
Does SmartSheet have a way to list out all of the predecessor rows that feed into a given row? For Row 22 in the example below, I would like the formula to return the values 20, 19, 18, 17, & 16. Either that, or I would be looking for a list of all direct and indirect successors to a given row. Either would get me where I…
-
UPDATE - Health and Wellness Solution
Here is what I have built out so far for my Health and Wellness Solution. Please note some information has been redacted, including my Forms Dashboard which is where I go to log most of my entries, it is more for me to see, it's not as pretty as these are. I'm a dork and record many things just for the fun of it. I started…
-
Ranking when duplicates
I need help with a challenging formula. I have the following situation: Row# Value Score Effort Score Priority Score Rank* 1 50 5 45 4 2 70 10 60 1 3 65 5 60 2 4 60 20 40 5 5 70 25 45 3 If Priority Score = Value score - Effort score then rank by priority score (with greatest priority score = rank 1) if priority score is…
-
Dynamic view time-stamp issue
I am set up in CET, and I set up a dynamic view where the time stamp column (time created) in the sheet is transposed to the dynamic view with a +6 hrs. This is kind of inconvenient… is this an issue that can be resolved? e.g. 12:01 pm appears as 18:01 in the dynamic view vs. 12:01, 11:40 am appears as 17:40 in the dynamic…
-
Creating child rows with form selections
Newbie here! I have created a form for marketing requests. One of the drop down fields is for Marketing Avenues with 13 possible selections. I would love to find a way to auto create child rows for each avenue selected under the parent project row so we are able to assign those to different people and keep up with each…
-
Help need by IF formula
Hi all, I need support by set up of a formula. The case is: - If column 'Team' = Security AND column 'T-shirt size hours' = L = 2 days THAN column Days_Sec = 2 - If column 'Team' = Development AND column 'T-shirt size hours' = L = 2 days THAN column Days_DEV = 2 - If column 'Team' = Basis AND column 'T-shirt size hours' =…
-
Formatting cells based on today's date
I’m working with a date column in Smartsheet and want to apply conditional formatting based on the number of days since the event occurred. The goal is to automatically color-code the cells relative to today’s date. For example: Blue if the event occurred more than 730 days ago. Green if the event occurred between 365 and…