-
Formula - Multiple IF rules
I have the following formula working fine! In short, based on the value of the “Application/Product” value, I auto populate the value of a Contact column. =IF([Application/Product]1 = "Edits", "Jane Smith", IF([Application/Product]1 = "Scripts", "John Doe", IF([Application/Product]1 = "Matrix", "Billy Bob",…
-
Custom Output based on Multiple Variables
Hi - I'm relatively new, have watched videos and searched articles but I'm stuck. I have 50+ training products and a ton of potential job roles and location combinations. I'm trying to use SmartSheet as a "database" where I can select from a set of criteria and have that spit out a custom set of recommended training…
-
Word Count in smartsheet?
I have a column in my sheet that contains a list of names of people that are attached to that record/row. I would like to add another column that displays the number of words in that column - just a count of people attached. So if the first column says "Jane, Jack, Jay", I'd like the second column to say "3". I can't…
-
Calculate Difference Between Latest Entry and Previous Entry (Dates)
Hello! We are collecting weekly meter reading data (Meter Name, Reading Date, Actual Reading) via webform. I would like to create a calculation in a new column (within the sheet) that automatically calculates the weekly meter usage based on the meter name using current and previous entries by date. *For each Meter Name:…
-
Count IF Between Dates in Excel based on a name
Hello, I want to count the cells that contain a certain name in the assigned to column on one sheet only if the task start date is within a certain date range i.e the current week. For example, count how many times the "Assigned To" column on Project Plan sheet contains the name John Smith The two sheets are Project Plan &…
-
Combine formulas referencing the same cell without running into a circle reference
Is it possible to combine multiple formulas referencing the same cell without running into a circle reference? Similar to passing an object to a function. I would like to combine the 3 formulas below for efficiency sake rather than creating additional columns. =SUM([column name]24/ 1000) =ROUND([column name]24, 1) ="$" +…
-
Adding a $ to a Reformatted Number ending in "M" (Millions)
Hi, I am trying to convert a number in millions but shown as a thousand to the nearest decimal point and then add "M" to the end of the converted number. I understand the formula to divide by 1000 and then use the ROUND function to get one decimal point,but when I concatenate the "M" at the end I loose my dollar sign. Does…
-
How to FIND if the grandparent or parent has a particular word
Hi, Is there a way to find the Grandparent or Parent has a particular string/word? I have tried PARENT(PARENT()) and ANCESTORS(), didn't work. Appreciate any help.
-
Pulling Tasks into a Report Using Multiple IF(AND( formulas in the same cell
My ultimate goal is to pull data into a report from a project plan. I want to pull: * Current tasks (Tasks where TODAY falls between the Start Date and the End Date) * Upcoming Tasks (Tasks where the Start Date is within the next 7 days) * Previous Tasks (Tasks where the End Date is within the previous 7 days) * Late Tasks…
-
Budget vs actual calculation
Hello, I was wondering why the formatting of this document is auto-crossing out the actual calculation? Is it suppose to be doing this? Is there a way to make it not do this?
