-
Calculating a sum
Hello, I am hoping someone can help me figure out a way to do this formula. The following formula works: =SUMIF(Part@row, "022-7374-00", [PO Price]@row * [Bal Due]@row) I'd like to replace the "022-7374-00" with a whole column of part numbers on another sheet. If the Part@row matches any of the numbers in my SL Tab column…
-
Trimming all spaces
Hi, I am trying to apply a formula so that when meta data is input it is automatically trimmed to have zero spaces, which I know how to do in excel but does not seem to work in smartsheet. So, I have a column where we input license plates, but people always add spaces making sorting the column a little annoying as…
-
Reference another sheet with conditional IF formula
I want to pull in the actual data from Smartsheet file B from column B if column A contains text "yes". I want to pull the Column B data into Smartsheet file A. If attempted various IF formulas but not working. All I can do is simply link sheets but that data may change and incorrectly new data from Smartsheet file B.
-
Email Addresses from Contacts
Is there a way to convert / extract the email address from the values in a column set up as a contact? I have a need for the email address associated with contacts. Thanks
-
If Status = Resolved, can a field be blank, else display a calculated number of days?
I want to track "# Days from Submission, but still Open", and also track "# of Days from Submission to Resolved". Currently the formula I have listed in "# Days from Submission" allows the number to get larger every day, even once the "Status" is "Resolved". Looking for advice on how best to handle this; at a minimum, can…
-
Assessing a Project's health based on % complete against % of time passed in activity du
I am looking how to enter a formula in SmartSheet that works for me in excel. I want to get a 'proactive warning" if a project is not tracking well to when it needs to be completed. If TODAY() is 60% through the allocated time for an activity but my pecent complete is actually 25% less than 60% , then I get a RED. If I am…
-
Sequencial numbering based on Criteria
Can you please help with the following formula to work in Smartsheet: =TEXT(VLOOKUP(C3,LIST!$A$2:$B$12,2,0),VLOOKUP(C3,LIST!$A$2:$B$12,2,0)&"0")&TEXT(VLOOKUP(D3,LIST!$D$2:$E$17,2,0),"00-")&TEXT(COUNTIF($C$1:C2,C3)+1,"000") I am creating a numbering system that are criteria based but then want them to be sequential based on…
-
How to COUNTIFS with DISTINCT dates and other range/criteria?
Hi, I am trying to write a COUNTIFS formula, taking into consideration of X criteria for a range of cells in addition to DISTINCT dates within Y range. Can anyone share any insight how to write this in a formula statement? I'll provide an example below. I have a list entries in Smartsheet. They have dates associated with…
-
When cell is updated to new value it updates past and present cell values in target sheet
We have a sheet similar to a price sheet that populates a target sheet via index/match formula. Stakeholders submit orders via a SmartSheet form. However, I don't know how to update orders in the future not in the past. Past order should still be the same value / cost. Thank you in advance for your help. Cathy
-
How to allocate estimated work hours evenly across the duration of days
Hello, I am using Smartsheet for my Project Plan, and we are wanted to assign estimated hours to each task on the plan so that we can see if Resources are over-allocated in any given date range. I only have access to the legacy resource system, so I am hoping there is a way to do this with a formula. I have columns for the…