-
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…
-
Need an Formula to generate Categories column
We are looking for the Categories to auto generate from the sub-categories multi dropdown selection. We were achieved this by using the =IF(COUNTIF([Sub-Categories]@row, CONTAINS("Fruits", @cell)) >= 1, "Fruits") + CHAR(10) + IF(COUNTIF([Sub-Categories]@row, CONTAINS("Meats", @cell)) >= 1, "Meats") + CHAR(10) +…
-
Best way to set-up an index contains
Hello Smartsheet, I have a sheet with URLs from our website that I need to be able to flag by division. To do this I have set-up a master sheet with the division's name and base URLs. What I need now is a formula to grab the division names from the master sheet. My intent was to set it up as an INDEX(MATCH()) but I realize…
