-
Updating parent status from child rows in separate columns
Hello. I'm looking for a formula to update the Project Type column in a parent row to identify as "Digital" based upon specific contact entries in the Assigned To column in the child rows. For instance: -child row Task 1: Assigned To Employee A -child row Task 2: Assigned To Employee A, Employee B, and Employee C -child…
-
Copying multiple contacts into a cell
Currently HR fills out a SmartSheet for a new hire. They fill in the Practice Manager and Hire Manager from a contact list. I have a formula to copy these contacts each task that belongs to these two people respectively. This way, no matter what department, the correct people are assigned and we only need one SmartSheet.…
-
Backdate If Function
Hello, I'm trying to construct a formula that backdates a certain amount of days depending on the date in a separate column and another criteria. See example below. If Audi or Infiniti is chosen, it would back date 30 days and Lexus or VW 45 days from the Sell Date. I have the formula to backdate below but can't seem to…
-
VLOOKUP to Return Two Users
Hi! I need a formula that looks at two columns and returns an email address based on vlookup if one of the two columns is blank and two email addresses (again based on vlookup) if neither column is blank. The formula works well when one of the columns is blank and only one email address is returned. The problem is when two…
-
Counting Checkbox from parent and sub
Hi I am trying the below function to count the number of checkboxes in total: =COUNTIF(CHILDREN(), 1) + " of " + COUNT(CHILDREN()) but it is only counting the checkboxes of the main parent and not the sub tasks Subtask "Ticket for Singapore" is not counted
-
Need to create new sheet by merging 2 sheets
Hi Community, Hope all are doing well, I need to merge sheet 1 and sheet 2 by creating new sheet i,e sheet 3. Ex: sheet 1 + sheet 2 = Sheet 3 here i need to make sheet 3 dynamic i,e if I add any data in sheet 1 & sheet 2 , it should automatically reflect in sheet 3. I m attaching 2 screenshots please suggest how to achieve…
-
Use of Averageif while filtering data in multiple columns
I am attempting to average %complete by team (Team#) where sessions are active (Sessions Scheduled). This requires two HAS(@Cell, Criteria) but I am getting an #UNPARSEABLE =AVERAGEIF([Team#]:[Team#], HAS(@cell, "1" AND [Session Scheduled]:[Session Scheduled], HAS(@cell,"1"), [% Story completed]:[% Story completed]) How…
-
IF/AND Formula with Dates
Hi all, I am trying to create a formula that will apply an X to a cell if the selected Date is between two other Dates (ie. Check In and Check Out dates). Currently I have a formula similar to what you would use in Excel: =IF(AND([TUESDAY 4/20]17 >= [CHECK-IN]@row, [TUESDAY 4/20]17 <= [CHECK-OUT]@row), "X") Check In and…
-
Is there a way to simplify this formula?
I have 3 columns with dropdowns of 'Complete' 'In progress' and 'Not Started'. In a fourth column (Percentage of Task Complete) I have a formula that assigns a value to each of those text items and then does an average and converts it to a percentage. Here is the formula: =(SUM(IF([Step 1]@row = "complete", 100, IF([Step…
-
Smartsheet Copy Rows Automation is Duplicating Data
Hi All, I have a Smartsheet set up which includes an automation to copy the rows to a second Smartsheet which works as a Pro Forma sheet for our Accounts Department. The idea is that we will input data into Cells as normal and then once our Client has approved the proposal, they will 'Approve' in Smartsheet via a dropdown.…