-
Two column types in one column work-around - #DATE EXPECTED error
Hi, I'm in the process of transferring a spreadsheet into smartsheet that splits costs by months, and I've ran into a #DATE EXPECTED error for my formula. I can't change the column type as the formula itself requires the cell above to be a date. Does anyone know if there's any work-around? Here's the formula:…
-
How can i calculate the % Complete at a sub-parent level based on multiple criteria?
Hello and thanks in advance for any assistance. I have spent a ton of time attempting to solve and am tossing up the flag. I have a sheet that needs minimal column formulas due to the line/cell count potentially hitting the base reference limits. I have an overall % complete being calculated on the sheet itself already.…
-
Can't Set Column Formula for Duration - Work Around Available?
I have several sheets that are tracking the time a field employee is working at a specific location. This is determined by the Current Date minus Start Date to give me the number of days. I set the output column to be a Duration column so that it would automatically input the "d" at the end. For my purposes, this needs to…
-
#UNPARSEABLE for Column "IF" Formula
I get an #UNPARSEABLE error for the following formula and am not sure what I'm doing wrong: =if([Destination]@row] = [Source]@row, [Destination ID #]@row, "") Where the destination = source, the destination ID should be entered. Otherwise, I want to leave the cell blank so that someone can manually enter the ID #.
-
Smartsheet not recognizing a number value
I have a sheet with item numbers and Dates when a purchase order is due. Some items have multiple purchase orders outstanding, so I have a MIN(COLLECT statement to find the earliest of the open PO dates for that item. What has me baffled is that for "Item" that has a six digit number, it doesn't return any value. If it has…
-
JOIN Muliple values dropdown menu
Hi, I have a simple JOIN formula to combine the cells on the right into a muli value dropdown menu column. However, for some reason, it will only show unique value and sometimes it wouldn't show it in order. Column "Join Formula" is a simple text/number column, with the same formula as "Join formula w/ muli dropdown". It…
-
looking for help with a formula that uses one "text", and one "match" or "has" condition.
the closest I have gotten to getting some results is using: =IF(AND(CONTAINS("Q8", {Expenditure Report Collection Range1}), HAS({Expenditure Report Collection Range 2}, [Primary Column]@row)), "ok","missing") maybe I don't understand order of operations but it's giving me a "ok" on a cell that should be missing. I need to…
-
Adding 1 more condition to AVG () with Collect() formula
I have successfully used the below to average the aging for help tickets that were reported between 2 dates. I need to add one more condition, so that it only provides the average if the severity of that ticket is Urgent or High (using @row). I cannot figure out where/how to add this third condition. =AVG(COLLECT({Closed…
-
Is there a way to create a search box?
We are wondering if anyone knows how to build a public facing sheet that has a search box where customers could type in a word or two and then have it return any hits. The data that would be searched could be linked to another sheet and that file would not need to be viewable on the public one. Is this possible?
-
Auto- fill information from another smartsheet
How can I make my smartsheet pull columns of information from another sheet? The data I want to have duplicated is entered first on a vendor intake sheet, the two columns I want to 'link' or duplicate are a unique name and contract number (in separate columns) and I want to have them pre-populate on a contract deliverables…