-
weighted task %, % complete by time
Hi, I'm having trouble wrapping my head around how to produce a formula for my scenario. We are considering each task (hierarchy 1) a percentage of the overall project. For example, research is 5% of the project, production is 25%, etc. For simplicity sake, I would like to report % complete based off of time elapsed for…
-
Index/Collect with a date being one of the criteria
Hi, I'm trying to write an index/collect formula and one of the criteria is that the date is yesterday. I'm having trouble. Any suggestions? =INDEX(COLLECT({Evergreen Totals}, {Admission Type}, "Start of Business", {Date}, TODAY() - 1, 1)) Thank you!
-
Formula not working in Smartsheet when data is populated from form
I'm using a form to populate my Smartsheet. If N/A is selected from a dropdown list the completed column for that section should be checked (see below). I can get it to work when selected in the Smartsheet using formula =IF(([Loan Servicing]@row)="N/A",1) but not if the selection N/A is being populated from the form. Thank…
-
Generating dates based on dropdown choice in another cell
Hi everyone, I'm very new to this and trying to generate a formula that will calculate deadline dates based on dropdown choices in another cell. If we get customer queries by phone, we have a deadline of 2 days' resolution; if we get them by email, it's 10 days. I would like to be able to auto-generate deadline dates for…
-
Simple COUNTIF Formula Suddenly Stopped Working?!
Hi I have had a very simple countif formula, that has been working with no problems for months, suddenly start retuning a #INVALIDOPERATION error. I have reviewed the formula and can't see anything wrong with it, has anyone else had the same issue? Have Smartsheet released something and broken this? TIA
-
#NO MATCH error
Hey everyone! I seemed to have ran into an issue using a vlookup formula. When I first wrote it out it worked for that cell but when I dragged it down the whole column it no longer worked. =IF(VLOOKUP([H145 A/C #]@row, {QG Schedule Tracker Range 4}, 2) = "Not in SS", "No", "Yes") This checks if the aircraft number in that…
-
sumif with children
Hey everyone I am trying to set a formula that I believe should work but keep have an error returned. What I'm trying to achieve is in the box that says #unparseable I want it to sum the numbers below it only if the status column shows Approved. I want this to happen in perpetuity for any children rows that are added below…
-
Nested IF FIND formula
I am trying to use an IF/FIND formula to validate a columns value to make sure it is a "valid" email address. It must contain "@xxx.com". I was able to find a solution to work with that scenario. However, I have discovered that if a user puts the email address in capital letters, it won't catch that. So "@XXX.COM" would be…
-
Formula adjustment to calculate date based on years duration
Hello, I currently use the below formula to automatically give me a date if I enter the duration. Now, this currently only works if I enter it in months. Can someone help me adjust this so that it calculates based on years duration? =IFERROR(DATE(YEAR([Start Date]@row) + ROUNDDOWN((MONTH([Start Date]@row) + [Duration…
-
#invalid Datatype Error
Hi all, I've a formula to calculate estimated hours. =SUMIFS({Master Summary - Estimated Hours}, {Master Summary Rollup | Live Date}, YEAR(@cell) = [Year Helper]@row, {Master Summary Rollup | Live Date}, MONTH(@cell) = [Month Helper]@row) The formula uses two references to a Master Summary sheet. Estimated Hours (from…