-
Nested IF troubles
I'm looking to do a NestedIF formula where I set up a predictive "Projected Ship Date" column to use additional columns to the below (old screenshot, but I figured it indicated appropriately enough). Not sure what part I have incorrect- but I am getting an #Incorrect Argument Set error. Formula I'm using: =IF([LNP…
-
How can I set a Row ID column that does not change when you move, add or delete rows
Hi all - I've been able to create a Row ID column that updates when you add/delete a row fairly well, but when rows are moved around it breaks again. Current set up: X: autonumber column Row ID: =MATCH(X@row, X:X, 0) This works ok but every time I move rows or make big changes I have to re-set the autonumber (X) column. I…
-
SUM formula
Morning all! I'm looking for a formula that counts the total score from my team summary, as long as the team name matches and populate it here, where it will then rank their score in comparison to other teams I have tried a number of SUM formulas, and cant get one to work for me. Any suggestions? Thanks in advance!
-
Reference a cell from another sheet
I am trying to reference a specific cell from one sheet in another. I've found formulas for reference a range of cells, but I just want one particular cell. Can anyone help?
-
What is wrong with this COUNTIFS formula?
=COUNTIFS({Consumer Alerts Sign-up Form Date}, >=DATE(IF(MONTH(TODAY())>=7, YEAR(TODAY()), YEAR(TODAY())-1), 7, 1), {Consumer Alerts Sign-up Form Date}, <=DATE(IF(MONTH(TODAY())>=7, YEAR(TODAY())+1, YEAR(TODAY())), 7, 31), {Consumer Alerts Sign-up Form Range 1}, @cell = "") I am trying to create a COUNTIFS formula for each…
-
What is the formula for This week, Next week, etc.
I had formulas to check one of 5 checkbox columns if a specified date (Projected Start) fell within "Last week, this week, next week, this month, next month" based on todays date. (see attached) =IFERROR(IF(WEEKDAY([Projected Start]@row) = 1, IF(AND(YEAR(TODAY()) = YEAR([Projected Start]@row), WEEKNUMBER(TODAY()) =…
-
JOIN(DISTINCT(COLLECT + JOIN(DISTINCT(COLLECT but how to remove duplicates due to using two formulas
Hi all, First post but I've been reading (and making use of!) many of the queries & answers for a while. Stuck on this one! I have two JOIN(DISTINCT(COLLECT formulas that I am adding together, but sometimes the same person's name will appear twice due to being pulled through by both formulas. How do I prevent this type of…
-
Scoring Matrix Formula
I'm looking for a formula to act as an automatic scoring matrix. The first part of the score is determined by a dropdown of 3 values (met = 0, not met = -1, and n/a = blank). The next and final part of the score is determined by a symbol color (green, red, and yellow). See image for visuals.
-
Remove spaces when concatenated field is blank [Field]@2row + " "
When using Join or concatenating via the + sign I am adding a space after each field. Sometimes not all 8 fields have a value but the space is still entered for blank fields. It looks good in smart sheet, but when you go to export it is has a space for each blank field. I would like to remove the extra spaces so when…
-
How to Track movement of Duplicate Code from One sheet to another one.
I have 2 sheets 1st is Asset Master sheet and 2nd is Material Movement sheet. An index match formula is implemented on Material movement sheet to get data from Asset Master sheet like Asset code, Asset name, Asset description. However there another Index match formula applied on the 1st sheet i.e. Asset Master sheet to get…