-
Nested If OR formula, Better way?
Hello, I am writing out a =If(or)) function right now and it will be way to long if i do it step by step and i am wondering if there is a better way to do this. So my formula is =if(or(Location@row="Place 1",Location@row="Place 2"), "USA",if(or))... I have about 150 places i need to attach to 20 different countries. Is…
-
How to correctly use INDEX(COLLECT())
I am trying to create a cross-sheet formula that pulls the data from another sheet if it meets 3 criteria. In the source sheet, I need the following criteria met: 1. "Month/Year" column to be the same as the "Month/Year of Report" column in my target sheet. 2. "Activity code" column to be "2105532" 3. "Name" column to be…
-
Meeting Minutes - automate MM#, author and attendees by date
Hi All, Hope you're all well. I wanted to filter the meeting minutes based on the selected date. I created hidden columns that contain the meeting number, author and actual attendees for that date. I tried using the formula below but it didn't work. The peach background means this is where I change the date manually, which…
-
Workday function
I want to calculate the working days from the start date. However, we have Sat as a working day which I have configured in the Gantt setting. (So we have 6 working days). When I use the formula WORKDAY ([Start Date],38), from 3-Mar-2024, I get the output as 16-Apr-2024 - which is correct. 3-Mar-2024 being Sunday. When I…
-
Future Date Calculation to a nth Day of Month
Hello, looking for help with this formula. Need to calculate a future date based on the [End Date] Conditions: (1) Only display a future date if [Ord Amt] is true; (2) Future date to be one month earlier then month of [End Date] unless; (3) [End Date] month = January, then Future date is to be December and one year earlier…
-
Form Confirmation Options
I have created a purchase order sheet and form which auto generates a number each time. How do I reference this number to pull it through into the form completion confirmation message and/or email?
-
Is there a formula that can check if a row has an attachment?
I have a dropdown, single-select column that describes the type of action item listed on that row. For example: If someone selects the Star symbol on that row, the column formula calculates that the action item is a Milestone and returns the "Milestones" option from the drop-down options. I would like to indicate when an…
-
Is there a way to restrict the data entered into a row through Dynamic View?
Hello! I'm working on creating an environment within Dynamic View for employees to enter their annual bonus deductions. Is there a way to prevent employees from entering information into two columns if the total of those two columns exceed 85? I have a helper column that lights up when the total is over 85, but I'd like to…
-
How to run multiple IF functions independently of each other in the same cell?
I'm having trouble figuring out how to get multiple "IF" statements to run simultaneously. I need it to check the presence of text in multiple columns, but the Value if True, shouldn't stop the formula from checking the other boxes, and it shouldn't only return a value if ALL conditions are met. So, I need an IF statement…
-
Automatically add people in Contacts column depending on the Country
Hello everyone, My team has a sheet with Submitter, Country, Recommendation columns. Each Recommendation is related to a Country. According to the Country, the Submitter field would have to be automatically filled with the respective people for the specific country. The Recommendations are added through a form. In…
