-
Subtracting data from more then 2 columns
Hello, I have been trying to subtract numerical data from three different columns, and I have tried =[Submitted HRs]@row-[Lunch Time]@row-[Contracted HRs]@row and get invalid operation I tried using parathesis =([Submitted HRs]@row - [Lunch Time]@row) - [Contracted HRs]@row and get an invalid operation I have also tried…
-
change status based on previous row
Is there a way to have a formula change the status column when the row above it changes. For example when the status in Row 1, changes to "Complete" the status in row 2 will change to "Ready to Start" I know I can't put a formula in the drop down so I imagine some sort of helper column would be needed. or could this be…
-
Join+Collect by rows
Hello, I am trying to create a smartsheet formula to aggregate summery information from an amendment sheet onto a master sheet. However, when I attempted to use join() +collect() my cell returned all date finalized collected then all #s collected and then all types collected. I want to return data in ROWS not COLUMNS. In…
-
How to adjust formula for stacked bar chart to use HAS or CONTAINS
I have a stacked bar chart that displays how many initiatives are assigned to an individual by year. (See example below.) We've recently changed the source sheet from only allowing one year per initiative to allowing for multiple years (for initiatives that span multiple years). How do I adjust this formula to count if…
-
Auto RAG based on milestones
I'm looking to write a formula that auto calculates a RAG on a row and struggling where to start as not my area of expertise. I've looked in the community and examples provided have not help me answer my question so hoping someone can help. My logic is if the Planned Start Date is in the past/overdue then it should flag…
-
Merged: Hyperlink transfer with VLOOKUP or INDEX(COLLECT)
This discussion has been merged.
-
How to create a sum column formula in Sheet Summary
I have a formula base column that I'm trying to get the sum in Sheet Summary and I'm getting the #invalid operation error. I have some criteria for the sum I'm looking for and I did the exact formula for the [DDA Hours Used] column, so I'm not understanding why this one isn't working.
-
calculate number of full months between start and end dates
I'm looking for a formula that will calculate the number of full months between 2 dates that will be used for calculating a goal amount by multiplying a monthly amount x the number of full months (partial months are prorated). In my example, I'm using =ROUNDDOWN((([End Date]@row + 1) - [Start Date]@row) / 30.425, 0) It…
-
Creating a widget for tracking the tasks for each member of a team
Hi. For the past few days, I've been trying to create a dashboard to track the tasks that each member of my team has. I learned that the best way to do this is to create a separate sheet where I used formulas to create a set of data and then use those to create charts in my dashboard. However, when I tried to use a formula…
-
A True Mind Bender - Tracing Tasks through Predecessors
Is this even possible? I have a task (say Task 18) that is preceded by other tasks, all joined by predecessors. Task 18 is the "final" task. Task #18 is preceded by Task 16, and Task 16 is preceded by Task 14. Outcome - I want to filter rows to see only the tasks that have a relationship to "Task 18." I'm thinking that I…