-
Index/Match Cross-Sheet Formula Help Request
I am stuck on what probably is a fairly simple cross-sheet INDEX/MATCH formula but this evening, for the life of me, I can't seem to crack it. It may be that I need to sleep on it, or it might be that in a few days I will be leaving on vacation for two weeks and need to get this complete and over to my supervisor before I…
-
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?