-
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…
-
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…