-
Can someone suggest where I'm going wrong with my formula
Hello All I'm fairly new to Smartsheet, and I believe my formula is correct, in all rows but 1 it is functioning properly. If I re-enter that information in the "bad" row into a new row it still flags incorrectly. The bad rows total 567.79 clearly under the 3999.. but still flags Red. The formula =IF(TEC@row < "3999",…
-
Populate Contact List Column with Value from Another Table With Date Condition
I want to populate cells in a Contact List column that is used in workflow automations based on a formula that pulls the contact(s) from another sheet. Sheet fields: Resource(s) (type Contact List that allows multiple contacts per cell) Start Date (type Date) End Date (type Date) Position (drop down - single select) My…
-
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.
