-
Multi-select string operation
Hi all, I have three columns which have the same drop down options: requested, received and pending. I would like the "pending" column to have all options from the "requested" column except for what's in the "received" column. For example: under "requested": option 1,2,3,4; under "received": option 1,2; under "pending"…
-
Trying to create a Projections sheet and need some suggestions please!
Hello everyone. I am hoping someone may be able to give me an idea on how to create a projections sheet for our engineering projects. I would like to build it out so I can spread the total hours per staff type over the duration of the project. I have tried a few different ways and cannot seem to figure out how to create it…
-
Lookup & Display First Non Empty Cell In A Range of cells within a column
I am attempting to write a formula in a header cell that returns the first Non-Blank row in a series within the related column. Excel permits this by using the Index and Match formulas but requires that "CONTROL, SHIFT & ENTER" be pressed for the formula to work. When attempting this in SmartSheet, I receive an "#INCORRECT…
-
IF/Match
Good afternoon, I'm having a problem with the below formula. I'm trying to do a formula that will check Invoice numbers on two sheets. If they match it will say yes, and if there is no match it will say no. However I'm getting Invalid Data type when there is a match, and No match if they don't match. =IF(MATCH(Invoice65,…
-
RYG Formula for Parent and Children Rows
Hello, I am trying to create a formula where the parent health turns Red if there is more than 3 red children health's, Yellow if there are more than 3 yellows, Blue if everything below it is Blue and the rest of the time green. Is this possible? I was using this formula for a while, but realized major gaps and it wasn't…
-
COUNTIF Question
Is there a formula that allows me to count the number of cells within a range that contain a certain string of text, where the cells in the range also contain other text?
-
RAG indicator options
Hi there community I have a portfolio of several hundred projects being monitored and reported via dashboards and reports in Smartsheet. We are currently using RAG indicators against schedule as follows: Red - Incomplete at reporting date or due date passed Amber - at risk Green - schedule within tolerances (+/- 10%…
-
calculating status based on status of multiple columns
Hi, I am trying to calculate the status of a main column ("status") based on the status of multiple columns (OP2-OP8). The idea is to automatically change the status (on the left) from "Open" to "Complete" based on the individual status of the columns on the right (Open/Closed). Background: we try to monitor the issue…
-
Reporting like pivot or sumif. How to roll up values by name or category?
Is there a way to combine cell entries and give the total assigned to that category? Ex. Project Lead Cost savings John $100 Mary $500 Bob $400 John $200 Mary $75 Bob $60 REPORT = John Total = $300 Mary Total = $575 Bob Total = $460
-
How to use multiple NOT in cross reference formula
Hi, I am implementing a formula which is taking cross reference from another sheet, let the column I am using be Column1, Let it have 10 different values. I want to get the count from this column where the value is not String1 and not String 2. I didn't find any usage with multiple nots, can somebody help?