-
Automation/Updating
Hi all, Hoping to get some suggestions on something I'm stuck with. I have a master sheet that I replace all the contents once a week (via a mass copy and paste). I have a group that needs to add data to one of the columns, however I'm trying to find a way of avoiding them from having any type of write access to the master…
-
Check a single box based on multiple Harvey balls
I have multiple rows listing activities for items A through J (arranged as columns). Each item has a Harvey ball to show its completion - e.g., half, three-quarters, full, etc. I would like for a checkbox column to be checked when all items' Harvey balls are full. What I want to do is this: =IF([Item A]@row:[Item J]@row =…
-
Formula to track duplicate text/values across multiple columns
Hi there, Apologies if this question has been asked multiple times but i have searched across the forums and cannot seem to find a definite answer to a problem i'm trying to find a solution for. I have built a tracker to track web activities across our site encompassing language, page that the activity occurred on along…
-
Prevent Cell Formula from being overwritten
I have a cell formula that will change the status of the parent cell (in this case Materials) to Green, when all of its children are either Green or Gray. However, since the sheet is shared amongst multiple people, it can be overwritten manually when someone changes the status to a different color then the properties of…
-
Cross Sheet Formula help
I have a sheet that has Employee information including a location code and job title. I have another sheet where I want to use a cross sheet formula to get the employees name when matched with the location code and a job title. So in other words i need a formula that will look at the location code on my sheet and using…
-
Current year formula
Hi Everyone, I currently have a sheet that populates mostly 2020 data. But we now would like to start integrating reports for 2021. Every individual formula has criteria for 2020, which I will now have to change 1 by 1 to 2021. I have an old excel sheet that allows me to change the year in 1 single cell, and then all…
-
cant get IF statement to change value of another cell
I am trying to use an IF statement to change the value of "Ball in Court" based on the "PM question" cell: FYI - both "Ball in court" and "pm question" are dropdowns =IF([PM Question]@row = "02 - Changes Needed", [Ball in Court]@row = "01 - ENGINEERING", "") When I set "PM question" to "01 - Submitted" the formula doesn't…
-
How to lookup a company name based on multiple contacts in a cell?
I'm trying to figure out if there's a way to create a formula that can look at MULTIPLE contacts in a cell and determine if ONE company matches from a project directory. In our Action Item log for the "Assigned To" field, this works fine if there is ONE contact: =IFERROR(INDEX({Dir-Company}, MATCH([Assigned To]@row,…
-
CONTIFS Formula - 3 Ranges including a multi-dropdown
Hello, I am currently trying to pull a count of all External Content types (ex. linkedin article, blog) tied to a specific pillar from a sheet (NOT using @row to count on master sheet). The pillar column is a multi dropdown. I need to count each time a pillar is associated to a content type. We may have Blog Post tied to…
-
Vlookup help please!
For some reason I can't get a V-Lookup to work when referencing another sheet. In the first attachment I am showing the formula. My intention is to look up the country (Mexico in line 1) against a separate Smartsheet showing the bank holidays for all countries and return the bank holiday. The formula I am attempting to…
