-
Excluding cells from COLLECT based on value
Hi All, I am trying to write a COLLECT which excludes results based on the value of a cell, similar to how NOT(ISBLANK(@cell)) works. Below is a formula I have made which works, but I was hoping there was a more succinct way to achieve the same result =JOIN(IF([Audio Language]153 <> "English", COLLECT([Audio…
-
IF FORMULA TO PUT AUTOMATIC STATUS
I would like to know what can I do to put automatic status for my monitoring sheet? There are 3 status: In Transit, Cancelled and Delivered. I want to know if its possible that when the requestor input the delivery date (refer to the screenshot), the status will change to "Delivered". For the Cancelled and Transit though,…
-
Most common text formula
IS there a formulas that collect the most common text in range of cells? I want a parent line to show the most common option in that column.
-
Copy contents of a cell into Comments
Is there any way to copy the contents of a cell into Comments? Or have comments on a specific cell? Or show the value of the last entry in Comments in a cell? Thanks.
-
How to create a future date based on a date in another column?
Hi, I am looking to create a formula in a column that automatically calculates the date of 12 weeks from the date of deposit column. Can someone please help with the formula? I keep getting errors. Specifically I would like the "estimated completion date" column to be the "date of deposit" column plus 84 days. Thanks! Keren
-
Using Auto-Generated Fields in Formulas Successfully
I have a sheet which tracks all a department's projects. We are making improvements which required us to create a new version of it by copying from a previous version (save as new) which meant that the auto-generated "created" date all the sudden became the date I made the new file, rather than the date the actual project…
-
Month Formula
Having some issues with the month formula popping up errors when being used as a reference for a range in the collect and countif/countifs functions The below formula produces an error =JOIN(COLLECT(Date:Date, Date:Date, MONTH(@cell) = 1)) The below formula returns a 1 even though every day in january is included in the…
-
Formula to Add Days to Calendar Date
Good Morning! So I was wondering if it would be possible to have a formula that would add 6 months (183 days) to a cell with a prepopulated date if a certain criteria is met. So managers will get a form and they will pick either "Yes" or No" for "Extend Vendor" and if they say "Yes" I want it to add 6 months to "New…
-
Max Function
I have a formula that finds and returns the maximum of a set of date cells meaning that it finds the latest date. It works fine if all cells are populated with dates but if one cell is blank it error out. This is the formula: =MAX([Due Date]1, [Family Member A Due Date]1, [Family Member B Due Date]1, [Family Member C Due…
-
SUMIF Not Equal To Formula
I am using this formula to Sum all values except for the ones assigned to "External"..It still adds these hours. What did I miss? =SUMIF([Resource Type]:[Resource Type], "<> *External*", Hours:Hours)