-
Returning a Date Value from Another Sheet if Certain Criterias are Met
I have been working on the formula to return a date value from another worksheet if certain criterias are met. Here is the formula i input but with a formula error of #UNPARSEABLE =INDEX(COLLECT({Send Out Date}, {Range}, =CONTAINS("Bank Confirmation", {Range}), {Client ID1}, =CONTAINS([Client ID]@row, {Client ID1}))),1)…
-
Remove a string of characters after a special charater :
Need a formula to remove "Account name" from the Use case name. Right now the format in the use case column is Account Name: Use case Name I want the "use case column" to only have the use case. I tried the below formula but getting a circular reference error. Please advice. Thank you. =LEFT([Use case: Name]@row + ":",…
-
using VLOOKUP with Dates and Text
I have two sheets, one which keeps track of dates and reminder dates which is maintained by team and looks like this: and another sheet which drives a form where people pick dates (a copy of the dates in Long Date above). The "target Appointment Date" is a drop-down value field i.e. TEXT. In the field "reminder 1" i would…
-
Sumifs with multiple matching criteria in target range
I've been trying different things with this formula, but to no avail. Here is what I am trying to capture. If the status in (Financial Master Range 4) is either, "committed" or "obligated", I want the sum of these two amounts with those statuses from (Financial tracker Range 5) to appear. =SUMIFS({Financial Tracker Master…
-
Change Cell Automation Based on Count of Items
Hello I am trying to create an automation that a waitlist column is "checked" when the total registrations exceeds 10. I've done this before using the auto-number feature to count registrations as they come in from the form, and then created the automation based on that column. However, I am trying to do this now based on…
-
EMAIL REMINDER 14 days before due dates
I have a basic sheet which list multiple policy dates for several providers. Each provider has a list of a round 12 different document renewal dates so i need to automate a reminder email to the person monitoring the provider 14 days before the renewal date is due but cannot figure out if it's possible to do this for…
-
COUNTIF and HAS
Hi I'm trying to find the best way of counting particular options from a drop down list on another sheet by a certain date. The formula I have so far is: =COUNTIFS(HAS({Job Log l Job Type}, "CD Creation"), HAS({Job Log l Job Type}, "CD Extraction", HAS({Job Log l Job Type}, "Scan & post", HAS({Job Log l Job Type}, "USB…
-
Display URL when condition met
Hello Experts, i have two sheet having unique column app name, i have URL column to capture data. i tried - if([app1]@row = {app2} ," https:URL", "") i want to show the URL when app1 and app2 are matched otherwise i want to show blank.
-
MEDIAN() Column formula keeps breaking.
Hello Smart people, I have a solution that uses four sheets. The first, is an export of vehicle assignments from a vehicle management system. Data Shuttle overwrites all data in this sheet at 12:00AM each day. The second sheet (Daily Rental Summary), is a sheet to aggregate daily totals. A workflow adds a new row to this…
-
Sum values from another grid based on multiple columns & values, and cross sheet row matching
Hi, im trying to get the correct formula for the following: From the Change Request Register sum the 'Cost Impact' values that have a 'Status' of Approved or Closed. Display those total costs in the 'Approved CR (£) column for the appropriate project - where the 'M&S Job Number' rows match on both sheets. There may be…