-
Destination sheet 'Project' column to auto populate values in dropdown form from Source sheet
Problem 1: I have Sheet 1, where Projects are tracked with 'Project Name' column and I have Sheet 2 with 'Project Name' column. I would like for Sheet 2 'Project Name' column to reference Sheet 1 'Project Name' values in the form of drop down for user to select. Sheet 2 has a form, in this form, I will be using the Project…
-
Formula for looking at invoice month and assigning quarter
Hi there, Formulas continue to be the one area I just can't wrap my mind around. I have a sheet where I have a column called Invoice Month. The values possible for this column are: 01-Invoiced JAN 2021 02-Invoiced FEB 2021 03-Invoiced MAR 2021 04-Invoiced APR 2021 05-Invoiced MAY 2021 06-Invoiced JUN 2021 07-Invoiced JUL…
-
How can I have boxes checked for all distinct employee ID if a payrate = a specific value (0.01)
I currently have a formula that will look at the pay rates for the same Emp ID and if there is a difference, it checks the Attention Pay Rate box in all rows with that Emp ID. This formula is: =IF(COUNT(DISTINCT(COLLECT([Pay Rate]:[Pay Rate], [Emp ID]:[Emp ID], [Emp ID]@row))) > 1, 1, 0) NOW, I have created a helper column…
-
SUMIFS - How to include multiple "Criterion" in a "Range"
I am sure there is a simple solve but it doesn't seem to be working for me. I am trying to use the COUNTIFS formula, with 2 ranges in included. The first range ("Status") is straight forward as I only want to select one criterion ("In Progress). In the second range ("Division"), I want to select multiple criterion (i.e.…
-
Check box if child row contain value of parent row
Hi, I have trouble figuring out a formula that will check the box of the parent row if child row contains the value of parent row. And the formula must work if it's a column formula. Thank you in advance!
-
IF Formula % Complete to Status
Hello - I need some help. I think I am close but my formula isn't working. This is what I'm trying to accomplish: ------------------- % Complete => Project Status Blank = Not started less than 100% = In progress 100% = Complete ----------------- =IF(ISBLANK([% Complete]@row), "Not Started", IF([% Complete]@row) < 1, "In…
-
Possible Solutions, anyone?
Hi Smarties, One of the users I am working with right now would like to have the following built in Smartsheet. Request Intake sheet > requester to enter the Task Type, Date Submitted and desired date for completion, and I will build a formula to estimate start date and estimate end date. I have managed to build a formula…
-
Determine if a Date is equal to or greater than 3 months ago
Looking for syntax help. I have columns (This Month, Last Month and Previous Month) to support some dashboard reporting IF statements. I am now looking to add a column called ">=3months" and I am unsure of the syntax. The syntax below is what I am using to determine Last Month: =IF(AND(MONTH([Record Date]@row) =…
-
SUMIFS & Specific Text/Quarter Requirement
I am trying to write a formula for a dashboard that sums the amount of money we spend a quarter based on a specific text. This is what I have so far and I'm getting "Invalid Operation". =SUMIFS{Log Sheet Range 2}, {Log Sheet Range 1}, CONTAINS("Pens", @cell), AND(({Log Sheet Range 7} = "Q2"))) Log Sheet Range 2 is the…
-
IF OR Formula
Can I get help with this formula? I need the answer to be 4 if either of these two columns are a yes. If both are a no, then I need the answer to be 0. If one is yes and one is no, then the answer still needs to be 4. =IF(OR([Innovate Audit]@row = "Yes", 4), ([Innovate Audit Included in Upgrade?]@row = "Yes", 4), 0)