-
Rounding full numbers (percentage) to nearest 10
I am trying to find a formula to round in full numbers (not decimal places). I have a column that creates a percentage of "investment paid", and I want to round this to the nearest 10. Ie; if the percentage result in the column is 59%, I would like this to display as 60%. And conversely, if the percentage result is 61%, I…
-
Formula Help - Automating Quarters Based on Date
Hi there, I'm trying to create a formula that will automatically pull Q1, Q2, Q3, or Q4 depending on the finish date of an activity. I worked with someone from the ProDesk, and he showed me how to create this using the normal quarters of the year. The problem is that my company's fiscal year is a bit different, with Q1…
-
Counting Unique Values in a Column of another sheet
Hi there, I'm looking to count the number of unique IDs in another sheet I'm referencing (that I cannot edit), and put the count by each ID in another sheet I'm using. I'd appreciate any tips that could help me especially keeping in mind I cannot edit the reference sheet (There are examples similar to this but they require…
-
#Unparseable Error
I'm really struggling with an Unparseable error with an IF formula. I've read all of the threads, and tried to apply the suggestions, with no luck. I'm wanting to apply value to dropdown options. Example: Yes is worth 5, No is worth 3. I'm using this formula: =IF([Inaccurate Content?]1 = "Yes", 5) , IF([Inaccurate…
-
Index, Match with AND formula
I am trying to create a index, match formula were I need to match 2 criteria's. is this possible? I tried the following with AND . This returns "INVALID DATA TYPE" =INDEX([Column4]68:[Column4]72, MATCH(AND([Column2]68:[Column2]72, [Column8]68, [Column3]68:[Column3]72, [Column7]68), 0))
-
Help with an inventory tracking formula
i have created a sheet that will track inventory for a given property. I am having to cut and paste information from an excel spreadsheet (from our supplier). I have some formulas built in to the sheet that will calculate if a given line item is checked off as completed. If the information pasted into the sheet extends…
-
Join/Collect Criteria
I am using the following formula in Sheet 1 to collect a list of CRNs (course registration numbers) from Sheet 2 (the ranges in the formula reference columns in Sheet 2):: =JOIN(COLLECT({AutomatedCourseCopy-CRN}, {AutomatedCourseCopy-CRN}, <>"", {AutomatedCourseCopy-UseinBCC}, <>"", {AutomatedCourseCopy-College}, CRN1), ",…
-
Time in 24 hours
Please help! I am working on a IT Change Request sheet where the request is submitted by Forms. For a change to take place we require a specific time to be entered. I know Smartsheet currently does not have a time option, but is it possible to have the Implementation Time, whether it's entered as AM or PM or in a 24 hour…
-
How to set a cell value based on another column. If false do nothing to original value
I have a status column that is a dropdown list. I also have another date column "Goods and Services Received" that pulls a date using a VLOOKUP from another smartsheet. What I am trying to do is change the status column to "Complete" if there is a date in the "Goods and Services Received" column of the reference sheet, but…
-
Can anyone spot an error in this formula?
Hi all, I'm having trouble combining <>s in the OR function. Using the formula below I receive one answer: =(COUNTIFS({TACTIC actual delivery date}, >=(DATE(2019, 1, 1)), {TACTIC actual delivery date}, <=(TODAY()), @{TACTIC tactic status}, OR(@cell <> "Cancelled", @cell <> "On hold"))) While editing the formula to split…
