-
Counting the number of projects started in a year
I'm trying to count the number of projects starting in a year. I am using the formula mentioned below - =COUNTIFS({Portfolio Roll-up - Start Date}, YEAR(@cell) = [Primary Column]@row) where Primary column has the number "2021". I am getting an error "#INVALID DATA TYPE". Both the columns (Primary and formula column), are…
-
Unparseable Error
Good morning, I'm new to Smartsheet. I'm getting an error when I add the following formula. I'm trying to get it to return the current project phase that is less than 100%. Do you know what I'm doing wrong? =IF([% Complete]37<100%,[Task Name]37, [% Complete]54<100%,[Task Name]54, [% Complete]75<100%, [Task Name]75, [%…
-
Cross Sheet Formula..
Hello! I am trying to build cross sheet reference that will tell me how many of a certain project are happening in a particular month.. However we had start and end dates we don't track anything by month. So my logic is =COUNTIFS({ Range 4}, "All Regions", {Range 1}, ="NAME", {Range 3}, >"Blue", {Range 5}, "BAU", {Range…
-
MAX and COLLECT to return most recent value not the highest
Hello! I am currently trying (and failing) to create a cross-sheet formula that will look for the most recent date for a given lot and then return a specific value from that date. Sounds simple enough right? I have one column on my metrics sheet that correctly pulls the most recent date for a given lot: =MAX(COLLECT({Assay…
-
Form AutoFill Send me a copy of my responses
I want to make sure a copy of all responses goes back to the person who filled out the form. I provided a link using "?ECA=name@domain.com". This works perfectly when the form is accessed from a laptop, but it does not work when they access if via their phone. Does anyone know why and if there is a way to ensure it is…
-
"IF" formla and Red/Yellow/Green
I am putting together a sheet that will be used as a template. There are 3 columns (Date Assigned and Date Due and Status). Status states Complete, Not Complete, and Not Applicable. There is another column that has the Red/Yellow/Green bubbles that should automatically update (red if late, yellow if within the date range,…
-
Change cell value
When we have an approval in one column (the value has changed from blank to approved) we need another column to change to generate a confirmation number What is the process for this?
-
Highlight Weekend Dates
Hi, I am super new to smartsheet and still learning. I have no previous experience with formulas. I have a column that has dates in it, but I want them to highlight red or something if the date is a weekend. Is this possible? What would the formula be if it is? Thank you in advance for any help.
-
Cumulative Column Formula with Exclusion
I have a cumulative column formula created that cumulatively adds up months, if we're in May, it will add Jan-April, if we're in June, it will add Jan-May, etc. There are some metrics the customer wants to be excluded from this formula and instead, have the formula provide the last month entered. I created a checkbox…
-
Auto fill Form
I have a form that I'm sending to users via email with a link. When they open that form, I'd like the date and name to auto-populate with the current date and their name. Information: The sheet that will be receiving this information could have their name in row 1 if that would help, but I don't want to already have rows…