-
Did I make it too difficult to calculate?
Hi, I am a rookie when it comes to formulas - and while I understand the basics, I feel stuck with one thing related to a sheet that will start coming data collection soon, and I want to ensure the formulas are all set before it starts. Here is where I am stumped: I have a column on my smartsheet where one option let's…
-
Formula to show date in Fiscal Year
I located the formula to create a Fiscal Year from a date (January to December) ="Q" + ROUNDUP((MONTH(Date@row) / 4)) + " " + YEAR(Date@row) 1/7/2021 = Q1 2021 But our company Fiscal Year is November 1 to October 31. I cannot figure out how to edit this formula to work. Thanks!
-
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()))