-
Formula to add today's date when ALL checkboxes are done.
I have a list of tasks and each task has 5 child tasks. All have separate due dates, they have a completion check box and a completed date. I have an easy formula in the completed cell to auto-populate the date when the completed box is checked and it works great! =IF(Complete@row = 1, TODAY()) What I'm trying to do is put…
-
How to attain an average to the lowest level hierarchy
Hello, I'm trying to attain a formula that would provide an average of the lowest level of children in a hierarchy. Currently I'm using a =AVG(CHILDREN()) formula, but based on the amount of data we are collecting, the highest most level of hierarchy is beginning to vary significantly based on the fact it is only capturing…
-
Index/Match, Collect, or Vlookup
I am trying to pull dates over from another sheet and matching on a unique value that is present in both sheets. However, the source sheet has the same unique value repeated multiple times and I want to return the date that IS populated. 1) {Schedule - DRL CMP WEQ Range 3} : this is the source range that contains the data…
-
I wish to compare a particular date in formula itself.
For eg - =IF($[Date1]@row > "1/4/2020", COUNT($[Date2]@row, "B") Right now my approach is updating the formula column properties as Date and updating 1st row as "1/4/2020" then comparing this with Date1. Issue with this is that the rest of the values in this column should be date or any string only. I want to add numeric…
-
"Health" Column Symbols
My Health Column Type is Balls I need to set the ball colors like this: Red if Due Date is before Today Yellow if Due Date is Today Green if Due Date is After Today Blue if Status is not "Complete" "On Hold", Cancelled" or "Rejected" This part works =IF([Due Date]@row < TODAY(), "Red", IF([Due Date]@row = TODAY(),…
-
Conditional Ranking? RANKEQ based off Category
Hello Fellow Smartsheet Users, I am having trouble putting together RANKEQ formula that ranks rows in my sheet by Total Aggregate Value, but only based off the Department. I would like to return top 5 aggregate values per each Department listed. Can anyone please assist?
-
Order Form that shows available Stock
Hi, I'm trying to create an order form. However, when they choose an item # from the drop down and if it's out of stock on my inventory sheet, I want the rest of the form to automatically grey out, or a message should appear. Is this possible? I started working on an idea, where I created a Dashboard with two forms. The…
-
SUMIFS not recognizing criterion when it is a formula
Hi, I have written a few simple SUMIFS that use the current month or current year as the criterion. However when I combine the RIGHT function in the cell with the criterion, the formula returns $0 (it should return $200). If I manually type 21 and set this as the criterion, the SUMIFS works. If I use the same formula and…
-
Count Children If
Hi There I have a status column using the ryg balls Red, Yellow, Green and Blue. I currently have a formula which will return Red, Yellow, Green and Blue if all the children are the respective colour. =IF(COUNTIFS(CHILDREN(), "Red") = COUNT(CHILDREN()), "Red", IF(COUNTIFS(CHILDREN(), "Green") = COUNT(CHILDREN()), "Green",…
-
COUNTIFS of a text string based on a condition
I have two text columns. One column contains titles of documents. The second column contains specifies the document's "category." One of the categories in the "Category" column is "Presentations." However, in that category are two different types of presentation: "BDM" and "TDM". Whether a document is a BDM or TDM is…
