-
What formula(s) do I use to reference a Multi-select column in another sheet?
Normally I use Index(Match()) to pull in the value from one row into another sheet. That doesn't appear to work when the source is a multi-select column. I've tried Index(Collect()) but can't seem to get the syntax right. Any help would be greatly appreciated.
-
Formula to return 30,60, or 90 if Date is within 30,60,90 days
Hello, I need a formula to return a value of 30, 60, or 90 if the due date is within 30,60,90 days of a due date. I am going to use this column to group a report to show each bucket of items due in the time frame. I can get the formula to return the correct value if the date is within 30 days, but am not able to nest IF…
-
HAS Formula Returning Wrong Results
Hey Community, I've created a formula to count, collect, and return results based on some criteria. I have also created a report that gives me what I'm looking for. At the report level the results come back correctly, but in my formula to just get a count. It does not. =COUNT(COLLECT({Intensity}, {TargetDate1}, >-30,…
-
Use Max function to return person's name?
Hi I want to use a formula that will return the highest value in column 2 is there a way to do this please
-
Issue with simple division formula
I have a sheet that pulls labor hours via cell linking. I want the neighboring cell to result in total hours / 4, but I am continually getting an #INVALID OPERATION error. In the below, I want CM App Hrs = 40. If I manually type 160 in CM Hrs, the formula works fine. All cells are Text/Number type (including the original…
-
COUNTIFS with greater than or equal to and less than or equal to date range
Hi, I'm trying to run a COUNTIFS on a Smartsheet that has a "Created Date" when a user submits a form. I want to count the number of submissions within a given timeframe, i.e., the month of January. I've tried the following formula: =COUNTIFS({In-Flight IFM Page Request - Created Date}, >=DATE(2022, 1, 1), {In-Flight IFM…
-
Hi - function to remove a flag with a checkbox
Hi All I am sure this will be simple for someone, however I can't find the right solution. I have a flag column which has the following formula to calculate and is very simple and works perfectly: =IF([Activity Start Date]@row <= TODAY(), 1, 0) However I would like this flag then to be cleared if one/or other of two…
-
Index Match Column formula returning wrong values sometimes
Hi All Thanks heaps for any advice. I have a sheet with a drop down of all my products. In the next cell i have an index match formula pulling the description of the product. It was working in the initial cell so made it a column formula. IT works for everything but as i add more rows the formulas seems to pulling wrong…
-
Can I get help on this formula? Not sure where I am getting it wrong.
I need this formula to be one value if Yes and the other Value if not Yes:
-
Conditional Formatting for highlighting rows if certain columns have data in them
Hi, I have an aging report in smartsheet and the last few columns are invoices that are past due certain time periods. If there is nothing past due, the cell in those columns are blank but there is still data in the row. I'd like to highlight the rows if these columns have any data in them. Is there a conditional…