-
VLOOKUP returning #INVALID REF
I am receiving the #INVALID REF error in smartsheet and I am not sure how to correct it. =VLOOKUP([PART NUMBER]1, [PN]1:[PART DESCRIPTION]1800, {2,3,4,5}, false) Below is the excel sheet formula that does work but does not flow over to the smartsheet. =VLOOKUP($Q6,$A4:$D25014,{2,3,4,5},FALSE) If you look at the excel…
-
Formula - Adding together certain assigned point values
Hello, I have created the below formula that works up until the bolded part. I am trying to add this to the below formula: If Less than 100 it gets zero points. If between 100 & 499.9 it gets 1 point If between 500 & 999.9 it gets 2 points If greater than 999.9 (or 1,000 or more) it gets 3 points. I am not sure how to add…
-
IF > TODAY formula not working
I've been trying for hours to get this formula right and I think I've tried every combination. I have a date field. It can either Have a date Be Blank Have TBD Have N/A If it has a date AND the date greater than today OR if blank OR if it's TBD = not check If N/A OR date is passed OR it's blank = checked This is what I…
-
Parent Health Status of Children Rows Based On If Certain Criteria Is Met
Hello, I have a Gender column with these options: Female, Male, Transgender, Non-binary/non-conforming, Prefer not to respond I need the parent row to reflect a status of RED ball if there is no 'Female' represented in any of the children. I currently have this formula, but feel I'm missing significant COUNTIF references:…
-
How do I set an automation without a trigger or is there a workaround to create a trigger
Hello! I am needing to set an automation to lock and unlock rows. I have the automation set to lock the row when it is changed from “open” to any other value. I need a time based function that will allow the cell to unlock a certain time period after it is locked by the first automation. Essentially I want to ensure that…
-
Is there a formula in smartsheet that would create a 2 way link between cells?
Hello, I have multiple sheets that I would like to link all into one Master Sheet. I want the cells to have a two linking methods between each other. If a cell changes in the Master Sheet, I want it to reflect to the other sheets and if the other sheet decides to change the cells, it would reflect back to the Master Sheet.…
-
Needing Help with an IF TODAY formula
After reading countless articles and trying countless combos, I still can't get this formula to work. I am trying to give a 30 day notice to an expiration day with a status column. =IF([TIPS Expiration]-TODAY()>30,"Green", IF[TIPS Expiration]-TODAY()<30,"Yellow"))
-
Budget v. Invoice Cost Codes
I have two Smartsheets. Sheet 1 is my budget broken down into various cost codes. Sheet 2 is a log of all my invoices with cost codes and amounts assigned to each invoice. I am trying to take the amount from each invoice and have them populate in my budget according to their cost codes. The budget amounts for each cost…
-
how to calculate how many staff are on site each hour?
Because of Covid, we need to know the max number of staff that are at the worksite each hour. When I had a "fixed" time frame for everyone, the formula was easy and made sense. Example, when everyone had 8:00 to 10:00 shifts and 10:00 to 12:00 shifts. But now I have random shifts and I don't know what formula I can use to…
-
Error w/ Formula (#INVALID)
Hi Formula Friends - Can you help me identify what's going wrong with the following formula (Reference Cell: 'Count1'): =COUNTIFS([Work Related Exp]:[Work Related Exp], 1, Created:Created, AND(DATEONLY(@cell) >= [Encounter Date]$2, DATEONLY(@cell) <= [Date Resulted]$2)) I was working a few days ago; however, something…