-
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…
-
Calculate formulas by Columns
I am relatively new to Smartsheet and am stumped on a formula, and so are my coworkers. I do QA for multiple teams and I need to be able to calculate information such as # of Exceptional scores (100%), Meets Expectations (85% - 99%), and Needs Improvement (0 - 84%). We tried Countifs, Averageif, and a few others and did…
-
Counting the number of items due in the next six months from today's date
Hi, I am struggling to find a formula that can count the number of items (each row represents a task/project) that are due in the next six months from TODAY (the date of viewing). At the moment the below formula is incorrect ... =COUNTIF([Target Closure Date]:[Target Closure Date], IFERROR(MONTH(@cell), TODAY(MONTH(+6))))…
-
Duplicate Values causing multiple emails being sent with the same info
So I have a sheet that is essentially tracking missed attendances. And we want to know when a miss happens each time. I have it set up currently to check for duplicates and then a box gets check for each miss up to 3. Then I have a workflow to notify different people based on the number of miss it was. My issue is that…
-
(SmartSheet newbie) Filter and Count across Columns
Hello, I am very new to SS (a day into using it) and I'm not even quite sure how to ask this question. I am trying to create a dashboard to see the total number of different types of positions in my company, with intersecting filters. I have a master sheet with a column for the type of position (Contractor, FTE, etc) and a…
