-
Dashboard count only if rows filled from a reference sheet
Hi. I am working on a dashboad to summarize some data from a reference sheet. I want to add a metrics widget that presents a sum of the number of rows on a reference sheet only if they are filled with a color completely. Details: I have a sheet with different project rows filled in green, indented under those are 'tasks'…
-
Can logic be added to a sheet?
My boss wants to create a sheet that includes 4 different profile forms. I'm curious if this is even possible. Basically, we have 4 different excel docs that are profile forms, we want to try to combine these forms on a grid in Smartsheets but we want the fields to auto populate depending on which profile form is selected.…
-
new GRANDPARENT() function
I would greatly appreciate a Grandparent function, especially since PARENT(PARENT()) throws an error. I know we have ANCESTORS(), but that gives you the number of ancestors and I need the specific ancestor value. Ideally, the GRANDPARENT() function would be GRANDPARENT( [reference], [# of generations back] ), that way it…
-
VLOOKUP: How to make into a Column Formula?
Good afternoon! I have the following formulas that I need to convert to a Column formula, any ideas? =VLOOKUP([Assigned Tech]2, {Team List Range 1}, 2, false) =VLOOKUP([Model Number]1, {Master Parts List Range 1}, 2, false) Thanks! Ashley Pierce
-
Formula for status based on Start/Finish Date and % Complete
I'm looking for a formula to populate Status column (Complete, Future Task, Late, On Schedule) based on the following columns: -Start Date -Finish Date -% Complete i.e. - If start date is in the future, it should be 'Future Task', if % complete is not 100% and finish date has passed it should be 'Late', if we are in…
-
Countifs Date range clash formula with Distinct value criteria
Hi all, =COUNTIFS(Start:Start, Start@row >= @cell, Finish:Finish, Finish@row <= @cell, 👤:👤, 👤@row) It returns a 1 as there is only 1 instance, but I would like it to return 2 because LM appears twice on the same date. This column allows multiple people to be selected. I was thinking maybe of combining a countM or distinct…
-
INDEX COLLECT, how to collect multiple values in a range when the criteria matches
I want to know all the employees those have salaries 13 using Index formula. Presently I am using the below formula, which provides only the first Employee getting 13 salary (in the cell belonging to 3rd column & 49th row) =INDEX(COLLECT([Column2]42:[Column2]47, [Primary Column]42:[Primary Column]47, [Column3]49), 1)
-
Counting Checked Boxes in one column
Hello, I am trying to count the number of boxes that are checked in the "Missed Information" column and showcase the total in the bottom right corner of the Sheet. I have tried many different formulas: =COUNTIFS([Missed Information:Missed Information], 1) =COUNTIFS([Missed Information : Missed Information], 1)…
-
Need a formula to add/subtract calendar days to a date to create a new date
Smartsheets seems to be defaulting to adding/subtracting business days, but I want the formula to use calendar days instead, how do I do this? My current formula is =[Planned End Date]1 - 195. But I want it to be 195 calendar days, not business days. How do I change it to be calendar days?
-
Need better solution for multiple nested IF statement
Hello, I have a sheet that tracks sprint numbers based on their date ranges. How can I identify the sprint number for a task using the task start date? Right now I'm using this very long formula in the Return sprint number column, and I'm hoping there is a better way to do this. Thank you! =IF(AND([Task Start Date]@row >=…