-
Multiple index/match nested in an If statement
Hello, I am having some issues with the following formula not returning the value I am expecting: =IF(CONTAINS([Raw Lot ID]@row, {ActiveRawLot}), (INDEX({ActiveQCResult}, MATCH([Raw Lot ID]@row, {ActiveRawLot}))), IF(CONTAINS([Raw Lot ID]@row, {ArchivedRawlot}), (INDEX({ArchivedQCResult}, MATCH([Raw Lot ID]@row,…
-
Formulas not processing for ages
This is a weird one, and it's been happening for some time. I have a sheet that people add to, in a batch copy and paste style. There are a few columns to the right of the batch data that process the cells and gleam certain information. It's nothing resource-intensive: e.g. in one cell we might have a person's team and…
-
Help with nested IF AND statements
Hello I'm new to Smartsheet and looking for help to convert a really long excel formula into Smartsheet. Is there a maximum number of nested statements? I have pasted it into Smartsheet as is, and get the Unparse error. Essentially, what I want to do is display specific text when two criteria are met. My formula in Excel…
-
How to Combine Multiple Formula for Matrix Table
Hi All, As we know, not all formula can be used in Smartsheet. Need help on combining "IF" "OR" "INDEX" & "MATCH formula. Currently we have a risk rating matrix where it depends on user selection on column "Consequences" & "Likelihood". Formula I apply in excel are:…
-
Multiple criteria in a row for VLOOKUP
I am trying to create a report from data with multiple criteria in a row. The objective is to return the residual risk value for each combination of risk + strategic initiative. In Excel, I was able to accomplish what I am looking for with this formula (=VLOOKUP(CONCATENATE(D$2,"-",$B36),'FY 2020 ERM…
-
Row Copying Issue
Hi I have a sheet, and in it there's a VLOOKUP to another sheet. I set a Copy Row Automation so that when the vlookup cell is filled, it should Copy the Row. I setup the Wrokflow like this: Trigger: When a Row is Changed When: <Field> Changes to Any Value Run Workflow When Triggered So, everything pulled into the sheet…
-
How do I return Last Match with VLOOKUP
Hello, My VLOOKUP function is always returning the first match other than the last match. The lookup table is updated chronologically, and I need to return the most recent data which will always be the last match. I'm open to other solutions that use other functions. Thanks.
-
Is there a way to write a formula that will raise a flag to if row data for household size AND ...
I am trying to write a formula that will raise a flag if the row data fits specific criteria for determining income eligibility for a project. Basically it would be if the data on that row in the household size equals a number and if the annual gross income is less than or equal to a dollar amount, we need it to raise the…
-
Snapshots and Logic Driven Archiving
Hey all! We are just about done and ready to parallel pilot our Request Management through Operationalization to Close solution and was wondering if there's an easy way (or hard way) to do the following: Use Case 1: Right now my teams' weekly status updates (which is a cross-sheet report between Intake and Metadata) keeps…
-
Approach to count new rows by day for reporting
We are using smartsheet to track issues from UAT and I want to be able to report on the number of issues added each day, and closed each so I can create a bar chart for the dashboard. I was able to calculate the number of new each day with this formula using the system created date column. I have a field for status and I…