-
Formula that will display the lowest value from two data sets (with the highest value).
Hello Smartsheet community, I need some help fixing a formula. The goal = Display the lowest value between (the highest value between 1A and 1B) and (the highest value between 2A and 2B). The formula I created is: =MIN(COLLECT(MAX(COLLECT([DATE 1A]@row, [DATE 1A]@row, @cell <> ""), COLLECT([DATE 1B]@row, [DATE 1B]@row,…
-
2 cells with dates -blank reporting
Hi, I have a cell "signoff" with date a that is manually entered and cell "required date" which is autogenerated from a look up. In 3rd cell "timeframe met", I'm trying to report if "signoff" cell is <= "required date" then report Yes, if not report No -but if "signoff" cell is blank it currently reports back "yes" so need…
-
Is there a function or formula to correct 1.1 hierarchy columns from being number and string values?
In this column I have highlighted the problem area. The smart sheet makes this into a decimal when there is one period, and makes removed the zero, breaking my hierarchy. I used this function in a helper column = Item@row + "" to make it into a string, but I'm left with the highlighted hierarchy as 1.1 instead of the…
-
Issue with dragging a vlookup to the right
I have a sheet with a table of vlookups, 3 rows deep and 15 columns wide. One date per row, and the columns have "standards " - gold silver or bronze. I have vlookups to return values in another table on the same page, and these all work. However, I need to replicate this in 24 other sheets....and seeing as I am unable to…
-
Using COUNTIFS
Hi All, Newbie posting. Been using smartsheet for my projects and keen to delve more into the reporting side for Board information. I have a Smartsheet that I use to record installs of certain types of products, we have three different types. I have used a formula to count the number of each type of product with an example…
-
COUNTIFS using Or
Hi all, Please can someone help me with the below formula? =COUNTIFS({POD Number}, "POD 1", {Policy Category}, "Plan Value", {Service}, OR(@cell = "No Service", @cell = "Advisory, @cell = "Legacy")) I am trying to get the count of POD Number being POD 1, Policy Category being Plan Value and Service being EITHER No Service,…
-
Formulae to calculate AVG days for Task completion meeting certain conditions
=AVG(COLLECT({Type}, "Plan Change", {Status}, "DocuSign Approved"), {Days to complete}) I am trying to calculate the Average no of days to complete the task. The {Days to complete} is a column where count of days is being populated by calculating the start and end date. The two conditions are 1. Plan change under Type…
-
Error Message HELP
What am I doing wrong, my formula was working then I tried to add the (@cell) function and now I get the error message
-
Date calculation that updates the year automatically
For my pipeline projection I'm trying to capture the forecast income from different projects (rows) for each month (column) over the next twelve months on a rolling basis i.e. from TODAY(). However, when the end of the year comes in range, the DATE formula does not change the year. Here's the formula in the column looking…
-
Identifying Duplicates
Hi 😀 I am having trouble working out a solution to a problem. I am identifying duplicates in a list of invoices as per the below using a COUNTIFS formula. The problem i have is when an invoice number starts with a zero it has an apostrophe at the start of the number and this throws the formula off which produces a false…
