-
"Review status" based on multiple checkboxes
Hello! I need help trying to figure out formulas that triggers the review status to green or red based on how many checkboxes that represent each month of the year are checked off. The boxes that are checked off would be based off on the frequency column which is semi-annually, annually, bi-weekly, daily, quarterly,…
-
Parent Status based on Children Statuses - Need help refining this formula
Hey Smartsheet Community! I have a beast of a formula that has probably gotten a bit away from me, but I need help refining this so it meets certain criteria per our stakeholders vision. Here's the formula: =IF(COUNT(CHILDREN()) = COUNTIF(CHILDREN(), "Complete"), "Complete", IF(COUNT(CHILDREN()) = AND(COUNTIF(CHILDREN(),…
-
Formulas not properly updating when referencing from another sheet
Hello! I have been working with a set of formulas that do not appear to be calculating correctly when I am referencing that sheet from an external sheet. Here's my problem: In my sheet, I have recorded a "created" column that uses smartsheet's auto generate date function to record the date that the line item was made. In…
-
Help with "AND" formula
I'm using this formula where I want to generate the average complexity score for any projects in the year 2019 where the product is ESP. However, when I use this formula I'm getting an "Invalid Data type". I've confirmed all references are correct. Anyone have ideas? =AVERAGEIF(AND({Complexity Year}, "2019", {Product},…
-
Simple Children / Parent question
Hi all, Apologies I took a break from working with SmartSheet for over a year and am slowly getting my memory back on how the formulas work. I have column called Helper, and in the CHILDREN cells it may say "Yes" if certain criteria are met. I want to have at the PARENT level, either in that column or a new Helper2 column,…
-
COUNTIF and OR or IF Formula
I'm looking to use COUNTIF to calculate if something is the right date and then if it has meets one of the following criteria (using references). My current formula looks like this: =COUNTIFS({Despatch Report Date}, =Date8, {Despatch Report Lot Status}, "Q", {Despatch Report WO Status}, <99 I basically want to count if its…
-
How do I bring a cell amount considering the start and end date correspond to that particular month?
HI everyone, Could someone help me solve this formula? I need to have the "revenue per month amount" cell (in gray) added to the corresponding months, considering the project will be happening in that month by reading from the estimated start and end date columns. I am currently using the following formula which is using…
-
IF statement with multiple conditions
Hello! I am currently working on an IF statement with multiple criteria and I'm having no luck. Basically, I would like the formula to articulate that if a specific manager is selected, then column 6 will multiply the data submitted in column 5 by a specific number, presenting me with its value. If Column 1 is one of the…
-
Formula to determine Duplicate items copied from one sheet to another
I have multiple sheets feeding this sheet. When items are modified changed updated or added they get copied to this master sheet. The newest items are the ones I have highlighted in yellow which I want to identify as the "Latest", that way I can have it auto move the out dated row to an archive sheet. This is the current…
-
Index/Collect does not work with < symbol
Hi there, I am using the INDEX COLLECT formulas to get a value if a number is between two ranges. The issue is that I'm getting an #INVALID VALUE error in the following formula: =INDEX(COLLECT({TotalRiskFactor}, {RangeStart}, >=[Risk Score]@row, {RangeEnd}, <=[Risk Score]@row), 1) The problem is with the "<" symbol in the…