-
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 >=…
-
Sum cost in row based on current date
I have a sheet that contains costs for various projects spread out across multiple quarters for 2 years. I'm trying to find a formula that will calculate [Billed to Date] in each row based on the current date. See attached example sheet. Essentially the cells in the [Billed to Date] column will update automatically each…
-
Manual Copy and Paste of rows fails to paste formulas and hierarchy
About 3 weeks ago I began being unable to copy and paste multiple rows between sheets in smartsheet. I have about 12 rows with several columns that match columns in a separate sheet. I am trying to copy rows from one sheet manually using CMD+C and CMD+V (I am on a mac) and paste them into the new sheet. I am able to select…