-
SUMIF Formula with Checkbox Criteria
Hello, I used to be able to use a sumif formula and pull in lines values that had a check box in one reference column or not checked in another reference column, and now that formula I used in the past doesn't work, I would use ...,{column Reference}, TRUE... ...,{column Reference}, FALSE... I can get around the issue with…
-
Pull data from one sheet into another using Index / match / contains
Hi everyone. I have two sheets and need to pull from one to the other. One sheet lists widgets and data about them, the second lists press releases and includes {press release tracking Range 2}, which is a column listing all of the widgets listed in that press release. So, the widget sheet is: Widget Press release…
-
Formula to calculate the Next [Future] date in a string of dates
Hello, I'm looking for a way to find the next task due date. Ideally, I'll be able to look at a row with several date columns, and determine the next date. This would be the MIN Date that is Greater than Today's date. I can easily get the MIN Date, However, many columns are in the past. I want the next [future] date after…
-
NESTED IF
Good day, I am struggling with this formula nested-if formula to automate the RYG status balls in the "Status "column, depending on the "% Complete" for each request. If a request is less than 70% complete, turn the "Status" column into a "Red" status ball If a request is 70% or above and less than 100% complete, turn the…
-
Index Match Formula
Hi everyone! I am trying to use the Index / Match formula to display data from different sheets into one. But I am having trouble getting it to match, I enter the formula as below but I keep getting no match. =INDEX({Johnson Range 1}, MATCH([Task Name]@row, {Johnson Range 2}, 0)) Any help would be much appreciated!
-
How to check a box if a date is in the past 7 days & next 14
Hello, I want to check a box if the following criteria are met. I want to then base an automation to send out rows that are not complete and were due in the last 7 days / due in the next 14. Criteria: Status is not Complete Finish is in the past 7 days (overdue) Finish is in the next 14 (look forward 2 weeks) This is the…
-
Creating a status using project stage
My data is a list of jobs all having 8 applicant stages. These 8 stages will ultimately represent 6 statuses. I am looking for a formula that will let me assign a status to each job based on the 8 applicant stages. Because jobs can have new applicants and an applicant in the offer step, I need it to work from the furthest…
-
look up value in other sheet when searching data isn't exact match
Hello I have a column in a project task sheet called Customer Name, where users enter the short version of a customer name: I want to populate line number in the project task sheet with data from a separate active projects sheet but the customer name in the Active Project sheet is not an exact match to the project task…
-
Cell Formula For Traffic Light Symbols
Hello, see attached screenshot. in the yellow cell (top right) I am wanting to have a SUM of the below 'Days To Complete' cells, but only those that have the Green traffic lights. As you can see this is a team members project list, so we are trying to estimate the work ahead for this team member. Can someone please help…
-
COUNTIF = BOOLEAN EXPECTED
Hi I want my smartsheet to count the number of checked boxes in a specific column. I have formulated: =COUNTIFS([Ceremony/Party]:[Ceremony/Party], 1). However, it is cominh up with "BOOLEAN EXPECTED"?