-
Alternative for Vlookup to search for a column rather than a row
I'm trying to simplify a formula that works, but will become too long for the cell. The Current Formula is =IF([Equipment Max#]@row = "", "", IF([Equipment Required]@row = "FOB", IF(COUNTIFS({EDBFOB}, >[Equipment Max#]@row, {EDBDate}, >=Start@row, {EDBDate}, <=[Last Date]@row) > 0, "Error"), IF([Equipment Required]@row =…
-
Metrics sheet - smartsheet limitations and quirks
This is a bit of an open question and I will try to make sense; I was making a metric sheet, made a set of metrics using a formula (iferror, avg, collect in one formula etc). Then I wanted to do another, with a similar formula, based on a different criteria range. So i copied the formula somewhere else in the sheet with…
-
COUNTIFS Function - 0-30, 31-60, 61-90, >90 Days
Hi, I am looking to pull the total number of unassigned projects with 0-30, 31-60, 61-90, >90 Days. The "Completed" column is the date the form is submitted. I am getting a value of 0 when using the formula below. Thoughts? =COUNTIFS(Status:Status, "Unassigned", Completed:Completed, AND(@cell >= TODAY(), @cell <=…
-
Symbol formula question
Hello! I'm looking for a formula which has the following rules... There is a sheet with a due date column, a date begun column, a % complete column (manual entry), and an Urgency column which will be the symbols with the formula. If you are past the due date and the task is not 100% complete, red If you are between the…
-
Vlookup to find result in child row
Hi all I am after help on a formula to pull in information from another smartsheet using vlookup but at the child level. I am looking for a match in my project to Ref, which is 699 and then find the data within the child row from Task 01. In this case, the result would be DC The reason for this is that we need to create a…
-
SUMIF spend total by year
Hey guys, I have been trying to write a formula which allows me to calculate the amount of spend by year but cannot figure out what i am doing wrong. I tried the following: =SUMIF(Debit5:Debit339, [Spend date]5:[Spend date]339, >=1 / 1 / 2021, [Spend date]5:[Spend date]339, <=31 / 12 / 2021) But get the error message…
-
Calendar View in a Report
I have our main schedule linked out to a report so we can filter each locations calendar. On the computer I can view everything correctly when I filter out certain locations. However, on the mobile app only a few of the events will populate. I have tried formatting the column to make sure they were all dates and filling in…
-
Cells not multiplying correctly.
Hi, I have two adjoining cells that are not multiplying correctly. I have tried =[part qty] * [module type] and the formula returns "10" when it should return "20" I have tried =[part qty]@row 8 [module type]@row and the formula return "10" when it should return "20" Any help would be greatly appreciated. [Module Type} is…
-
Approvals based on values in contact list containing more than one value
I have an approval process to ok team members to work on an additional project. I have a contact list column to enter which team members are being requested for a certain project. I need an approval that will fill in and gather approvals from their directors My initial thought was to use an index/match function to pull the…
-
Index/Match with two criteria: MATCH must use Search Type 1
Like the title says, I need to match on TWO criteria in an INDEX/MATCH function. The two lookup criteria are: 1) Phase (this one is straightforward) 2) largest value less than or equal to a given percent If I only needed to match on the second criteria, I could use INDEX/MATCH and sort the Source in Ascending Order by %. I…
