-
Formula for Milestone Health
I'm trying to figure out a formula for "Site Health". Each site has 9 milestones that will have a "Milestone Health" column. Each milestone must be completed a certain number of days before the project end date (October 31-22). I don't know how to create a formula to make the milestone health Green, Yellow and Red. At the…
-
Adding Cells together - but ignoring blank cells - problem with output.
I've created a Sheet that collects data from various sheets and collates it to enable a Cashflow Graph to be produced. The rows noted as "Forecast", "Actual" & "Forecast Cumulative" all work as intended with cells appropriately left blank where there is no value - this ensures the graphs don't bounce back and forth to $0.…
-
How to use OR for COUNTIFS, referencing 2 columns in another sheet
Hi, I have the following formula in a column, =COUNTIFS({Sheet Range 1}, [Column Sample]@row, {Sheet Range 2}, "ABC", OR({Sheet Range 3}, >TODAY(), {Sheet Range 4}, >TODAY() I need to do if column A = 1. column B = 2, (next should be OR) or column C = 3 or column D = 4 Please help out!
-
Help with sorting numbers and letters that are combined in one cell?
I would like to sort by a column that has both numbers and letters in a cell. Such as 1, 1a, 1b, 1c. Right now if I sort it by it by the ascending option, it's able to do 1a, 1b, and 1c correctly, however it separates it from the numbers that don't have any letters. For example: This is what it does: 1 2 3 1a 1b 2a 2c 3b…
-
INDEX/COLLECT Giving #INVALID VALUE
I have a sheet set up with a form for employees to enter their name, hours, and week ending date on a weekly basis. In order to create a dashboard chart to compare all employee's hours week-to-week, I've set up a separate sheet to reference the entry sheet with the week ending dates as the primary column and employee names…
-
Extract Date from Modified Date
I am trying to use the Modified Date column in formulas. For some reason, it doesn't appear that this was created as a "Date" column type. Because of this, it cannot be used in any formulas that would allow a date. Is this assessment correct? Also, is there a way of pulling the date out? I've pulled out the Month, Day, and…
-
week formula for retail fiscal calendar
need help with a formula I have for our Week columns. we operate on a Retail Fiscal Calendar, so we're 4 weeks behind the Smartsheet default, hence the "- 4" in my formulas. The formulas work great for February through December, but once January hits, the Week cells populate as -3 (Jan wk 1), -2 (Jan wk 2) and so on...…
-
I need to combine/create a formula that calls an icon
The below formula searches for duplicates in a column labeled 'pin' and adds a one to a 1 to a column named flag when duplicates are found. =IF(COUNTIFS(Pin$1:Pin@row, Pin@row) > 1, 1) I need a formula that does this but turns the 1 into a smartsheets symbol or maybe the word 'duplicate'; I have tried various ways and have…
-
Auto Check Box if Date Falls in Specific Range?
Hello All, I'm trying to figure out a formula to automatically check a box if a date falls between a specific range. For example, I want to check a box for Q1 if a project start date or end date falls between 1/1/2023 and 3/31/2023. Does anyone know of a way to accomplish this?
-
In regards to the Marketing Campaign Management Template
I'm using the Marketing Campaign Management Template that Smartsheet offers. I'm updating some parameters within the '3: Campaign - Plan' Sheet and have unhidden columns to see how everything is connected. I noticed there is a 'Campaign Code' and 'Campaign Name' Column that was hidden and has a column formula. I looked at…