-
IF Text in any field between multiple columns, then equals text value
I am having some issues using the "IF" formula. I'm trying to make it so if ANY VALUE is in the "CUT, FAB, WELD" columns then it will output the text "In Progress" to the "STATUS" Column, and if there is no value in any of the columns then the "STATUS" column will say "Not Started". Along with this, I have a checkbox in…
-
Value function
Having trouble with the formula below. Version 1 works and extracts what I need from a cell. I need to then convert that to a numeric value. Currently I get #invalid value. I'm missing something, and I bet it's dumb. ;) * =MID([Active Link]@row, (FIND("/", [Active Link]@row) + 1), ((FIND("*", [Active Link]@row) - 1) -…
-
Count if dates are within the next 7 days
Hello, I'm trying to use the COUNTIFS function to search a reference column full of dates to count how many are within the next 7 days. For the criteria portion of the formula, I've tried using, "<TODAY(7)", "<TODAY() + 7", but they are not providing me with the correct (if any) result. I would like to avoid having to…
-
Data Validation
How can we validate data at cell level? I want different cell ranges (in the same column) give me different drop-down options. Right now I see that we can validate data at column level and not at cell level while Google Sheets and Excel allow validating data at the cell level. Please suggest should I do? Thanks!
-
VLOOKUP for attachment on another sheet
Smart sheet enables the user to upload attachments, which is useful. So what I do for my workflow is I have a full sheet dedicated for part number and part information, so when I type in a part number on the work order sheet it auto-fills the rest of the information such as description, assigned person etc using the…
-
COUNTIFS for 2 and 3 criterion
Hello, I am trying to have my formula count when the row has "Ken Mallory" for the name column and any date that equals January. This is what I have so far but it is Unparseable. Thanks for your help:) =COUNTIFS({Master Compliance Sheet Final ver 1.0 - 12 Range 2}, "Ken Mallory", {Master Compliance Sheet Final ver 1.0 - 12…
-
After Reducing Cross-Sheet References
Hello, I hit my maximum cross sheet reference of 25000 - I removed a SUBSTANTIAL amount of columns that were pulling useless information (we realized we didn't really as much information like we thought we did.) But - since removing a high quantity of cell links, my sheet is still not calculate the remaining…
-
SUMIFS - Multiple Formulas
Hi I am trying to calculate the revenue for each leg of an event. The drop down options are "Spring", "Fall" or "Both". Using the following statements individually I can get part of the answer: =SUMIF(CHILDREN(Roadshow21), "Spring", CHILDREN([Forecast Amount]21)) =SUMIF(CHILDREN(Roadshow21), "Both", CHILDREN([Forecast…
-
Nested IF Statement with RYGB Balls
Hi, I'm trying to setup a nested IF statement to do the following: If DONE is checked then change the Status to Blue. If Due Date is more than 8 days out then make it Green. If Due Date is 7 days or less then make it Yellow. If Due Date is TODAY or in the past make it Red. This is what I have so far but it is not working.…
-
Keeping persistent dates for status changes
Building a kanban board with typical IT cycle states: Ready, Dev, Test, Deploy, Done. I'd like to track cycle times and throughput. I've created (locked) columns to capture a status change for each of the cycle states; e.g. Dev Start and formula "=IF(Status@row = "Dev", TODAY())". That works to capture the date that a task…
