-
COUNTIFS not working correctly
I wan to count the number of projects that meet these criteria: Maintenance project, not an improvement project Not canceled (so status could be "Completed", "In Progress", "Not Started", etc. Matches a term code (e.g., 202110, 202090, 202060, etc.) Here is the formula I am using: =COUNTIFS([Project Type]:[Project Type],…
-
How if a box is checked it any point in the process it will mark it checked for that section
I have sheet that has many items on one that all have a similar process. If one is marked sold at any point (any child step) it will check all the sold for that particualr parent and children. This will stop the notification for that particular item. I do not want it to check the boxes for the whole column because it will…
-
Reporting: Multi-Select Data, Best Practice?
I have a multi-select column I'm trying to count instances of for each quarter of each year. I created a make-shift report using a sheet and I'm looking for help to determine if there's a better way to do this. Report I created: I've duplicated that sheet into more columns so I could share the formulas I am using. The left…
-
Current Week vacation formula
What did I mess up in my formula to calculate from another sheet, the number of drivers taking vacation in the current week for my dashboard? =COUNTIFS({Vacation Intake Sheet Range 1}, "<=" & (TODAY() - WEEKDAY(TODAY(), 1) + 7), {Vacation Intake Sheet Range 4}, ">=" & (TODAY() - WEEKDAY(TODAY(), 1) + 1)) For context, the…
-
Need another option than INDEX MATCH
I am trying to pull data from 2 columns in one sheet into another (a name and a date) which worked great until there was another instance, which know I find out that INDEX MATCH can only see the first instance. I am looking for other options. Below is a quick example: Formulas being used are in bold and underneath are the…
-
#UNPARSEABLE using COUNTIF and cross-sheet reference in 2 colums
Struggling and would appreciate any guidance…………. I have a huge Master sheet and I have created separate metrics sheet to be utilized in a dashboard. In the Master sheet, there are two separate colums, one for various departments and the other for Approval status of PO's. In my master sheet when i run the formula below it…
-
Copying rows to new sheets based on multiple selections in one column
Hello, new to Smartsheet so might not use the correct language, but hopefully someone understands what I need! In my company we host CPD sessions where suppliers will come in for an hour over lunch and teach us something new. We need to keep a record of these sessions and who attended them. I have set up a sheet where I…
-
Automatically enter date at start of month
Hello, I have a Smartsheet that I'm using to calculate monthly metrics. I would like the sheet to auto update as time passes to add a new line for the new month. The sheet currently has a column titled "Start Date" and the first 10 rows of the sheet are the first day of the month from Jan - Oct 2024. The automation I have…
-
formula to generate unique ID for this scenario
Hello all, So I am trying to work out how to do a formula to auto generate a sequential ID starting from row 18 like in the screenshot example. When a form is completed/submitted it will update each row automatically so was hoping also that the formula will update accordingly when the form is submitted and saved in reach…
-
Convert Quarters to Date
I have a list of dates that are Quarter/year (in a format like Q4 2031, Q1 2038, etc.) and would like to use a formula to show the last date in the quarter. So: Q1 2031 = 03/31/2031 Q2 2027 = 06/30/2027 Q3 2033 = 09/30/2033 Q4 2035 = 12/31/2035 I would appreciate help creating the appropriate statement!
