-
Average Collect Issues
Hello! I'm trying to write a formula that will average the amount of time a type of request takes to complete by month. I'm referencing another sheet in this formula and just can't get it to work. Here's what I have so far: =AVG(COLLECT{Hours to Complete}, ISNUMBER(@cell), {Request Type}, "Quote Request", {Submitted Date},…
-
#Unparseable If Statement
Hello, I am relatively new to Smartsheet and was trying to write what I thought was a very simple line of code but keep getting a #Unparseable error and uncertain why. Below are 2 If statements that I have tried with the same results. I verified header names to ensure accuracy but same result each time. =IF(AND(Due…
-
Checkbox formula for this month and last month
I have 2 checkbox columns with column formulas. One column to identify if the line was Created This Month? and one to identify if the line was created Last Month?. It seems to work just fine, unless the line was created after 4:00 PM (or maybe 4:30 PM)... then it works the opposite... This Month? formula:…
-
multiple VAT % sum
hello all I think this simple but I cant get it right for sum reason I have 4 columns VAT % , NET Price and VAT, plus Inv total basically VAT % has a drop down of different vat charged 6 options I want my formula to look at option picked in column 1 (VAT % ) and the amount in Column 2 Net price to tell me in column 3 ( VAT…
-
IF Formula with Multiple Criteria
Hello! I have a software enhancement sheet, and I am trying assign priority to each enhancement based on the level of effort (LOE) + change priority calculated score. The LOE can be one of the following: 1 - Unknown, 2 - High, 3 - Medium, 4 - Low, 5 - None The change score change range from 4-25 The breakdown for priority…
-
COUNT/NOT/OR function?
Hi! I'm looking to add a function in my sheet summary titled "Other". I want the formula to count the number of grants that have a status of "Green" and do NOT have the following funding source "TEA - Federal", "TEA - State", or "Local". We will add more funding sources in the future, so using NOT would be ideal. I have…
-
IF with INDEX function
I'm trying to elaborate a formula that only brings up a cell from a month of the year (ex: january) that is connected to the year we are at now (2023) and the year before (2022). So when we are in 2024, the cells of the months that come up are from the year 2024 and 2023, and etc. For this, I created a separate sheet, so…
-
Can I import new data to a separate sheet without a reference?
I have a Zap set up to add a new row to a Smartsheet when a student completes a course. I want to assign the student a voucher code from a list on a separate sheet, and update the Student info row with the voucher code. The index match column formula retrieves the voucher code from the other sheet when the email address…
-
Check parent task if children tasks have all been checked
Good day I need help to auto complete a parent task if all tasks relating to the parent has been checked? See pic below. I know you can use IF formula, but would like to use something a bit more automated using ANCESTOR function if possible. So that I can convert the values to a column formula Assistance will be…
-
Overall Project Status based on frequency?
Hi, So I have the following sheet set up for projects where each Group may have more than one project in different statuses: The order of status are the following: 1) Started, 2) In Progress and 3) Completed. I am trying to set up a formula that will return the most frequently populated Status per unique Group and also…
