-
Trying to create an IF THEN function in smartsheet
My example is to select an material size value based on the material. Something like this: If [cell1]="4X8 Sheet" then [cell2]=32, if [cell1]="4X12 Sheet" then [cell2]=48, etc, etc. I have a dropdown list in the materials column to restrict the set of possible materials. Subsequently, I would like to take those values to…
-
Sum if of Current Week +/- 1
I have a formula that will give me the current week capacity: =SUMIF(@{Week Number}, @cell = (WEEKNUMBER(TODAY())), {Capacity}). However I'm not sure of the formula to capacity for the next week or the past week capacity =SUMIF(@{Week Number}, @cell = (WEEKNUMBER(TODAY()) +7), {Capacity}). I would greatly appreciate some…
-
Nested IF statement with Checkbox
I have 3 fields. 2 fields are checkboxes and 1 is text. Active (checkbox) Not Active (checkbox) Complete (Yes/No) I want to write a nested IF statement that if either are checked it provides a Yes / No response. IF(ACTIVE = TRUE, "Yes", IF(OR(NOT ACTIVE = TRUE, "Yes", "No"))) I get the field to work how I want for Active…
-
Find a Match withing a Join?
How can I use MATCH to find if an instance occurs within a group of values joined with JOIN? For example, if I join the values in three columns (Red Yellow Blue), no commas just a space, and I want to MATCH the value in a cell (say, "Red"), how can I see if the joined values (Red Yellow Blue) contains Red? Right now, the…
-
Dashboard formula counting rows within date range
First post, Longtime SSheet user but new to formulas and stuck on this last one for a dashboard. I am trying to count rows where the due date is within a range, the form created date is within 14 days of the due date and the status is not "complete." I have tried a wide variety of solutions. =COUNTIFS({FAMS Marketing…
-
Nested Sumif Formula
Hello, While we're training new employees I need to track their progress over a 2 week period using 9 different forms that are filled out at the end of each day; which used to be done on paper. There are 4 different teams that send in trainees so I thought it'd be easier to create 4 different workspaces for each team,…
-
Help with rollup sheet structure and formulas
We have contractors that we pay every 2 weeks. I created a form for them to upload invoice info into. I also created a report that pulls the individual invoice totals. What i want to create is a rollup sheet that will give me a total of that week's invoices for a given contractor. So for instance...Total invoiced amount…
-
COUNTIFS + FIND function help
Hello! I am trying to count the number of rows with a non-blank cell in one column and a blank cell in another column. The non-blank cell contains a contract number that looks like 18-A-001-SH and everything but the leading 18 changes from one row to the next. I started with this formula but it is returning a value of zero…
-
Formula for when if a checkbox is checked...or not
What would be the formula that would return "True" if a checkbox is marcked as checked and "False" if it is not? Thank you!
-
Changing column Property Status (Red, Yellow, Green) when two columns dates are different
I am in need for the status colum 1 (red, yellow, green) to automatically change when column 2 (date) is passed column 3 (date). Can this be done with a formula? I can do "=IF([Approved Required Delivered Date]1 < [Adjusted Delivery Date]1, "Red")", however, I am sure on how to add the other two conditions in the formula.…