-
I want to count a cell if it contains any kind of text "*"
Hi there, I'm trying to find how I can count how many times a cell contains anything within a column. I know there's no wildcard like "*" in Smartsheet. Is there a workaround without inserting a specific text? I'm trying to replicate the formula below from Excel into Smartsheet. =COUNTIFS({Column X}, "Purple", {Column…
-
Status Circle Automation
Hi, I am using status circles for a high level status report. Is it possible to automate the 'Overall' Status Circle based on the colors in the other measures (budget, timeline, scope, KPIs, value). For example, if 4-5 of the metrics are green, Overall is green. If 3/5 are green, Overall is Yellow. If 1-2 are green,…
-
Find the row with the earliest Created (Entered) date that matches certain criteria
This one was a challenge. I thought I had it solved, but an erroneous value showed up, necessitating taking the formula back to the drawing board. The requirement: Show me the oldest Bill of Lading number that does not have a status of "Closed" or "Canceled." In another cell, show me the full Created date and time of that…
-
Duplicate Dates Across Multiple Columns
Hello, I'm trying to highlight duplicate dates with conditional formatting across various columns to avoid conflicts. Is there a simple way to do this? Sample Data is below. Thanks in advance.
-
Count # of Parent Rows in a Sheet
What formula can I use that simply counts the number of parent rows in a single sheet? My scenario: Each parent row is a different customer. I want to know how many active customers a particular PM is working with and link that number to a metric widget on the PM's internal dashboard.
-
How to Summarize the Assign to: Contacts in a Report
Hi There I see there is plenty questions relating to this topic, but what ever I try doesn't work. Here is what I'm trying to do: I want to create a Report on Multiply sheets. Each sheet has different Contacts assigned. Now I'm stuck on the Sheet Summarized Option. Here is my Screen Shot 1st Question - Under Summary Sheet,…
-
Vlook up Error
Hello Everyone I am using vlookup to return a contact card based on an email but for some reason, it keeps showing me #No Match even tho the email is found. Here is the formula for the Vlookup: and here is the ref table: I am using this formula in a different sheet and it works fine but it doesn't look at the email so i…
-
IF Formula for "ball color"
I am creating a sheet where you have to with a dropdown menu for each calender week (CW) in which you can choose between "fulfilled", "partly fulfilled" and "not fulfilled". I want to connect these field with another column where I use the ball symbols (fulfilled = green, partly fulfilled = yellow, not fulfilled = red) I…
-
How to apply conditional formatting when one date column approaches/exceeds another date column?
Column1 is Target Date Column2 is Current Estimate Can I make it orange and yellow depending on how far the Current is from approaching the Target?
-
Countif with Dates
Hi all, I've read notes on other posts with similar dates but haven't been able to figure it out so asking here. Sorry for the repetition. I'm trying to count the number of cells in a column of dates that have a date prior to a date I have stored in another cell. I'm actually doing it with countifs but I can get the other…