-
Populate calendar holidays given an agent list and the company holiday calendar sheet
Hello Dear Community, I have a situation with a leave calendar in Smartsheet, I need to add all holidays for the agents given the office/location, I have the list of agents with the 3 digit country code and a list of holidays with the same 3 digit country code, description and date, I want to populate all dates for each…
-
Need Formula to capture all instances of text alone or with other text in a column
Currently using this formula to count all instances of a matching status with EBIT division. Sometimes EBIT is not the only division listed in the column. So a cell might have EBIT TBI. This formula works only if EBIT is alone. =COUNTIFS({Status Phase}, [Primary Column]@row, {Sponsoring Division}, "EBIT") I tried adding…
-
I am getting an invalid data type when the IF function pulls the false statement which is blank inst
=SUM(IF(AND(YEAR(INDEX({Draw 1 Date Received}, MATCH([TK ID#]@row, {FEE TKID}, 0))) = 2024, MONTH(INDEX({Draw 1 Date Received}, MATCH([TK ID#]@row, {FEE TKID}, 0))) = 11, DAY(INDEX({Draw 1 Date Received}, MATCH([TK ID#]@row, {FEE TKID}, 0))) < 15), INDEX({Draw 1 Received}, MATCH([TK ID#]@row, {FEE TKID}, 0)), ""),…
-
Creating a Unique ID (YY-###)
I'm looking to create a unique ID per entry as YY-### that will reset each year (24-001, 24-002, 24-003, 25-001, 25-002 etc) Row ID: (auto number column) YYYY: =YEAR([Date Submitted]@row) YY: =RIGHT(YEAR([Date Submitted]@row), 2) Entry ID: =YY@row + "-" + IF(COUNTIFS(YYYY:YYYY, YYYY@row, [Row ID]:[Row ID], <=[Row ID]@row)…
-
Using IF with CONTAINS and AND for multiple columns
I am looking at two different columns - one is called Status and the other Priority. I would like to have a formula/function setup that will look for "Open" within the Status column and "High" within the Priority column. If it finds "Open" and "High" together in any row, I want a "True" value returned. This will be used in…
-
Advanced VLOOKUP
I have a sheet 1 having column object name as ZCONXXX (where XXX are numbers) and another sheet with task name having tasks for execution like “Review and upload ZCONXXX” and I need to update duration in sheet 2 matching object name column in sheet 1 to task name column in sheet 2. Help please
-
IF level@row = X, Display short version of dashboard link, else calculation
Hello Community, I have a series of columns that are summarising the output of complex projects. I would like to be able to have a "Link to Dashboard" at the top of a column that is a column formula. The link would provide the end user with instant access to more detail if required. Ideally if anyone has a solution, the…
-
Automated Notification Based on Number of Days and Selections in Multiple Smartsheets
Hello, I'm hoping someone might be able to help or at least get me on the right track. What I would like to do is have an automated notification go out to the "(D) Assigned to" from SS2 if they are assigned a state/territory in the "(C) Out of State" cell in SS2 that contains the same state/territory as SS1 "(B)…
-
Using IF with Find
Hi, I'm trying to simplify a cell's contents, to allow better cross referencing and help eliminate issues with typos. I create cells with a serial number, someone requests that serial number though a form. Since the serial number can have a lot of variations requests through the form can have typos leading to the cross…
-
Updating one formula reference is updating all formula references.
I have several formulas that reference a different sheet. When I update one formula it updates all other formulas that reference that sheet regardless that I've pointed them to different sets of data on that sheet: For example - the above formula references the sheet Lafayette Square but the column with dollar values that…
