-
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…
-
Locked rows not picked up by automations
Hi all, I have a spreadsheet with just 4 columns, and i have a set of rules to apply to them. One of those rules is sending Update requests if the date column is over 7days in the past. But, since the spreadsheet will have "countless" rows, i want the rows that had Update requests before, to be locked (easy part) after the…
-
Removing System Generated column (Date) contents
I have completed building and testing my sheet and Dashboards. I now want to remove the System Generated Date for all my Form entries used for testing. If I remove the column and reinsert it, the formulas dont work. Is there a simple way of removing the system generated date data?
-
Formulas
Hi, I have a formula that works perfectly in Excel but I can not get it to work in Smartsheets. I am having issues finding corresponding functions in Smartsheets. This is the formula and screenshots show what I am getting from them.…
-
SUMIFS Statement under Parent Item
I need to Sum the "Total" column cells IF: 1) Column Department Allocation Code = "49 CONTINUOUS IMPROVEMENT: Formula: =SUMIFS(Total:Total, Department Allocation Code:Department Allocation Code, ="49 CONTINUOUS IMPROVEMENT", Item:Item, =PARENT(Item1)) *Not working <img alt="" data-entity-type="" data-entity-uuid=""…
-
"IF/AND" returning #INVALID DATA TYPE"
Hello, Guys Please, can you help me to fix this formula? =IF(AND([Start (plan)]4 < TODAY(); [Start (real)]4 = ""; "Red"); IF(AND([Start (plan)]4 = TODAY(); [Start (real)]4 = ""; "Yellow"); IF(AND([Start (plan)]4 > TODAY(); [Start (real)]4 = ""; "Green")))) It is returning "#INVALID DATA TYPE". The "Start (real)" and "Start…
