-
Moving Rows to another sheet
Hello! I am looking for some assistance to see if this is possible. My company is utilizing SS for our staffs productivity. Currently it looks something like this in order for my formula in the "From Previous Month" to work staff must remain in the exact same order as it is not a column formula example for "Sally Sue" in…
-
Index(Collect with multiple criteria
I'm trying to make a formula in a sheet summary that checks if the "duration" column is not blank and then uses all rows that arent blank as a range to then find the row with the newest "created" date and return a value from a different "IndexKey" column. I'm not sure if i need to use an IF staement to limit the range or…
-
SUMIFS a number is within a range
Hi Community! I am trying to come up with a formula that will sum a column titled 'Hours/Week' based on if a specific person is 'Assigned To' the project and a number (the Week # being evaluated) is greater than or equal to and less than or equal to two other numbers (the project's Start and End Week numbers). This is the…
-
Parent RAG light Formula
I cannot get the syntax correct. I am looking for a formula where the parent row will show a green RAG light, if all of the children RAG lights are green, if any of the children RAG lights are red, then red, otherwise yellow. The children rag lights have their own formula driven off a date which works great. I just do not…
-
Error with Convert to column formula
Hi, I have a formula that works for each cell in a column when I copy and paste it. However, when I try to "convert column to a formula" I get an error message saying my syntax is incorrect. =IF([Start Date]2 > TODAY(), "Blue", IF(AND([% Complete]2 < 1, [End Date]36 < TODAY()), "Red", IF(COUNTIFS(CHILDREN(), "Red") > 0,…
-
Issue adding a year to a date
Hello, I am building a formula to add a year to an existing date in my sheet. So it looks it's working but I realise it doesn't take into account the day of my initial date, don't understand why.. Please see bellow the example: =DATE(YEAR([Work at heights - Training date]@row) + 1; MONTH([Work at heights - Training…
-
Progression chart
Hi everyone, I have a questionnaire solution that only reports the latest attempt to answer it. It records how we as an organisation are doing against ICO Guidelines. My manager has asked me to create a dashboard that shows progress. So a line chart that will show how the organisation is progressing. My problem is the…
-
Formula for returning a value when multiple criteria are met
I am attempting to write a formula in a sheet to pull in a single value from another sheet when both the Area and yesterday's date match. I have tried this: =IF(AND({Area}, "Offsite", {Date Collected} = TODAY() - 1), {Daily Collection Amount}, "") and this: =IF({Area}, "Offsite", IF({Date Collected} = TODAY() - 1), {Daily…
-
Barcode or QR Code Auto-Adjust Inventory
Hello, Is there a way in SmartSheet that I can have the staff in our clinic scan a barcode or QR code for one of our consumables and have the inventory AUTOMATICALLY adjust down 1? I know it is possible to scan a barcode and then edit the cells automatically but this has it's own problems. The issue I need to eliminate is…
-
Sumifs based on quarter and year
We're creating a master project sheet. we'd like to be able to break out totals by yearly quarters. For example 1st quarter 2023. This is the formula I have working for 1st quarter total. =SUMIFS(Fee:Fee, [Preferred Date]:[Preferred Date], INT((MONTH(@cell) + 2) / 3) = 1) When I add a year criterion it fails - I imagine I…
