-
List of Unique Values for Dashboard
I'd like to get a list of unique values in a range. I'm finding a lot on the community about how to flag duplicates, but not sure how to get a list where each unique value appears in a cell. Screenshot below is what I'm looking for. My goal is to incorporate that into a dashboard: I want to see all unique values that have…
-
Can IF be combined with INDEX/MATCH AND ISBLANK to provide alternate value for blank results
I can successfully use INDEX/MATCH to look up values between sheets, but many of the results are blank, in which case I need to INDEX/MATCH to a different column of data. Is it possible to use an IF(ISBLANK), perhaps in a nested IF to return the 2nd-choice cell? For example: =IF(INDEX({Directory VP},…
-
IF, Else, Contains Formula
Hello, I have a formula in Excel that looks for key letters at the start of string text and if the condition is met, the cell is populated. The formula needs to be something like: IF [column]@row contains BM as the first two letters then test 1 IF [column]@row contains RH as the first two letters then test 2 IF…
-
Help Getting Started - Newb
I am having trouble calculating the total cost of units purchased in Smartsheet. I have tried using the formula =SUMPRODUCT([Units Purchased],[Unit Cost]), but I get the error message #unparseable. Can you please help?
-
If / and / contains formula
Hello, Trying to get a formula to check an "active" box if two conditions are presents. Essentially when both these if conditions are true. IF(CONTAINS(Docs@row, {Active Docs}, 1, 0)) IF(CONTAINS([Trade Partner]@row, {Active Clients}, 1, 0)) Trying the below gives me an #INCORRECT ARGUMENT =IF(AND(CONTAINS(Docs@row,…
-
Updating Project Stage Advancement formula
We have a current formula for Project Stage Advancement, that updates the Project Stage field when all tasks under in the section have been marked 100% and a task in the next section has been marked In Progress. =IFERROR(INDEX([Task Name]:[Task Name], MATCH(MAX(COLLECT([Start Date]:[Start Date], Status:Status, OR(@cell =…
-
IF AND COUNTIF formula not working for checkbox field
Hello, I'm working on a formula in a checkbox field that is checked when the nominee is listed more than once in the Nominee field (contact field) and the Previous Month and Current Month fields are checked. This formula is what I've come up with but it isn't checking the Repeat Nominee field when it should:…
-
Day of the Week Calculation
I am trying to calculate the day of the week based on a cell reference. I am a new user so any and all help is appreciated. So far I have this am I on the right track? [DueDate]2 is the hard coded date that is in question. =[Due Date]2() + (7 - WEEKDAY([Due Date]2()))
-
Calculate number of leave days per employee per month
Hello. I am working on two sheets: Employee Entitlements and Leave Register. Sheet 1: Leave Application, where all the employees submit their PTOs. Sheet 2: Summary sheet, holds the summary of each employees' leave balance, days used, and leaves incurred per month. How do I calculate the number of leave days per employee…
-
Formula to Copy cells if no match?
I'm looking for a way (probably some sort of Index/Match formula?) to check for a customer name on 2 lists. If the name shows up on the second list, but not the first, add it also to the first list. Here is our specific set up: The first list is a workflow tracker where most people will start. The first step in the…
