-
Formula to lookup a value based on criteria in 2 different columns?
I am looking for some help with a formula on my Roll-up sheet. I want to pull the Score by Site for the different years based on the information populated in my Data Sheet. Criteria #1: Site Name (e.g. Chandler, Chicago) Criteria #2: Year (e.g. "2018", "2019", "2020") Data sheet: Roll-up sheet example (where I'll plug in…
-
What is the best way to identify exceptions to a sign in/out worksheet?
I'm creating a sign IN/OUT sheet and am trying to build a formula that checks a checkbox if there is not a corresponding "Check Out" to a "Check In" based upon the guest's email. I need help building the formula to match the following logic: If email AND created on date match AND there is not a Check In AND Check Out…
-
Countifs Question
I need to calculate the number of times a person does a demo of a product within a month. Not adding the month I have been trying to use this formula; Ideally, I would like to the formula to work and include the count equal to a month.....
-
#Incorrect Argument Set"
Each of these pieces work on their own but when combined I get "Incorrect Argument Set" and I'm at a loss as to why: =IF([Percent Complete]5 = 1, "Green", IF([Percent Complete]5 >= 0.25, "Yellow"), IF([Percent Complete]5 < 0.25, "Red"))
-
Can I create one Smartsheet QR code that shows me a list of Forms to choose from?
Currently, my team has several different forms that we use to track process KPIs, or to populate other files. This means we have to post or carry around a handful of QR codes linking to those various forms. I'd much rather have one QR code to rule them all and show all the forms, click on one there and have it navigate me…
-
Expiry Dates - Compliance Check
Dear SmartSheet Community, I need your help. I need help in writing a formula that will track compliance for different areas based on expiration dates. If dates are active and will not expire in the next 30 days, health should be green. However, if one or more items in the row will expire in 30 days, yellow and anything…
-
How to reference another sheet in a formula
I've been trying to write a formula that will "Check a checkbox (in sheet A) IF [name] cell (in sheet A) is found in {name} column (in sheet B), AND the box in [checkbox] column (in sheet B) is checked." Basically, does a particular staff member's name appear on another sheet, and if so does it have a checkbox checked in…
-
Behind/Ahead of Schedule by "x" days
Hi All, I'd like to add a column that reflects how many days my project plan is ahead/behind schedule. I asked this question last year, but couldn't find it anywhere. I have a Planned Finish and a Finish Date. I'd like the row to display the following for each: If no variance, display "On Time" If late, display " X days…
-
Health check with 3 criteria not working
Hi All, it's probably something REALLY basic for you all, but I cannot for the life of me get my Health check status to convert from Red"No"/Yellow"Hold"/Green"Yes". I have written my formula as: =IF(ISDATE([LOA Issued]@row), "No", IF(AND([LOA Received]@row = 1) + (ISDATE([LOA Issued]@row)), "Hold", IF(AND([DRAs]@row = 1)…
-
Easy Date Question: Today plus 30 but nothing in the past
This one should be easy but every iteration that I've tried is not working. I would like the formula to first check the Expired column to see if it's checked, if it is, return "Closed", if it's not, check to see if it's today's date or 30 days out and if it is, return "Blue" (I would like to see any tasks that are within a…