-
create a due date based on report date and category
looking for a formula to do the following: Target due date based on category: Due date is report date, status is OPEN +number of days based on risk category High - report date +1 day Medium - report date +3 days Low - report date + 7 days Advisor - report date + 10 days then place the target due date in the column TYIA
-
Once due date is met, start new row based on recurrence interval
I have some reports that are due at different intervals, 1 year, 5 years, etc. I want to create a formula (or method) that when the due date is reached it will set the next due date. It would actually be neat if the system would generate the next line, but I can live with just doing the formula. And need to due this if the…
-
Stumped on a SUMIFS for Fiscal Year (FY)
Hello everyone, I am a bit stumped on a solution. I tend to not like to hardcode information into a formula. I was working through a solution to help manage a Business Development (BD) pipeline. With the help of the community, I figured out how to do a YTD. But then I was thinking that it didn't make sense to have the BD…
-
IF statement inside a COLLECT
Why do all of these formulas work: SUM(COLLECT({Value}, {Location}, Location@row)) SUM(COLLECT({Value}, {Location}, <>"")) SUM(COLLECT({Value}, {Location}, IF(index@row=1, "Boston", Location@row))) But this does not: SUM(COLLECT({Value}, {Location}, IF(index@row=1, <>"", Location@row))) I'm trying to understand this…
-
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…
-
Cell Linking
I have two sheets. One is titled Expiration Data and the other is Inspection Scope. On the expiration data sheet, I need to take the average of a column of only certain cells, and display that on the inspection scope sheet. The column is titled 'QTY Approved' and I need to filter it down to a certain part number, take that…
-
Formula for a PASS or FAIL
I would like to add a formula to the sheet that shows a Pass or a Fail depending on three other columns. If Mold Present column say "No" & the Seeds per 100g column says "None" or & the Foreign Matter column says "No" mark the cell as a PASS. If Mold Present column says "Yes - Beige" or "Yes-Grey" or "Yes-Black" & the…
-
Contacts in a column from another sheet to a separate sheet
Hi, I was wondering if anybody has an idea on how to accomplish what I am trying to do. I have a sheet where there is a column formatted as a contact list. There's approximately 100 rows, each with an entry of a particular person. Some people are duplicated in the row. I have a separate sheet (for a different purpose).…
-
I'm starting w/a criteria field, and then trying to find a first letter after the space.
So if the drop down option Time Away (Workman' Comp, FMLA, STD, etc.) is met, then I need to look at the name column, where names are listed as First Last, and grab the first letter of the last name. Alternatively, I could reformat the name to be Last, First via formula, and then just capture the first letter? Either way,…
-
Autofill Drop Down menu from Report
I have a PO Request database where I want to have users be able to use a drop down menu to choose the Approved Supplier, from a continually updating list. That list lives on another sheet, however. I cannot imagine that Smartsheet proposes that the only way to update this drop down is via manual fill. Is this correct? Or…