-
Looking to create a formula that automatically counts X of X and returns appropriate values.s
I currently have a formula that counts how many matching cells in a column there are. The cells are a combination of numbers and letters. For example: CA0059 or CD0101. My current formula only counts the matching number, so for example is CA0059 shows up in 4 different cells it will count it 4 times. My current Formula :…
-
Using Index Match with Parent Children
Hello I have a grid that is currently pulling the next due date from the table from the Parent/Child combo after an action is closed. I'm trying to put together an Index/Match based on that Due date and another Column in the sheet. I want to pull the action type on the same line as the next due date but am struggling in my…
-
SmartSheet Filter Issue - Please Help!
Hello all, I've run into an issue where one of my users added a filter called "Filter" to a sheet that has caused an issue. I'm looking for some help. When the filter is ON, the sheet has 900 rows full of data. When I turn the filter OFF, the sheet only displays 10 rows of data. I have included a screenshot of the filter's…
-
Check box formula help!
I am wanting to set up a formula and/or conditional formatting so that when you check one box, it automatically un-checks another. example: two columns side by side. one titled "job started" the other titled "job done." I want it so as soon as you check the box for job done, it removes the check from job started. This…
-
Name different weeks by IF(AND Formula and WEEKNUMBER.
Hello! I need some help to make my simple formula more efective! What I want is according to my [Start Date] know the week number, and depending on that number have another column saying if it's the current week, past 1 week, past 2 weeks, and past 3 weeks. So my formula is this one =IF([Week Number]@row =…
-
Flag duplicate titles but not if the title is blank
Hiya, I am trying to write a formula that flags entries if the content of the title column is repeated, but I need it to ignore entries that have a blank title. My current one is: =IF(COUNTIFS([Conference Title]:[Conference Title], [Conference Title]@row > 1, [Conference Title]:[Conference Title], [Conference Title]@row) =…
-
Automatically Copying a cell from another cell but with edits
Hello, I have 2 columns called " Location" & "Observation report Number". The Location column is a drop-down list of 4 options (WH1, WH2, JS1, JS2). I would like to have the "Observation Report Number" column to auto-populate with the location in addition to a number at the end of it. For example, If I manually enter the…
-
I have automatic color dots, how do I add the If status (Due Date) is complete, return gray?
=IF([Due Date]3 < TODAY(), "Red", IF([Due Date]3 = TODAY(), "Yellow", IF([Due Date]3 > TODAY(), "Green"))) currently using the above formula to automate color dots for task health, however when an item goes complete it stays as RED. I'd like it report out as a gray.
-
Multiply Different Columns w/ IF(Or
Hi, I am pretty new to formulas and am having trouble merging the two formulas below. I've tried IF and IF(OR and IF(AND, not sure how to do this. Basically, if there is a green dot, I want to multiply two @rows and if there is a blank I want to multiply two different @rows for the $ amount to be in the RENTAL COST FULL…
-
If a value in a cell equals the same value in a column from another sheet
Hello Smartsheets team, Hopefully a simple question, but I can't seem to get the right formula going for it. I'm just looking to see if the value in a certain cell from Sheet A (an invoice number) already exists in another sheet's column, Sheet B (essentially to prevent duplicate numbers). I've been trying with INDEX,…
