-
MAX COLLECT Formula
I have one table for shipping (Production Priorities) : And another table for Project Revenue Recognition (Revenue Recognition Forecast): I would like to write a formula to auto-populate the max ship date for each project. =MAX(COLLECT({Production Priorities Range 1}:{Production Priorities Range 1}, {Production Priorities…
-
COUNTIF HELP
I'm summarising and want to count a Project Status columns Have a sheet per colleague and want to do formula that would add up how many On HOLD projects there in total across all of those colleague sheets?
-
Adding Months based on Calendar Days not individual days
I am looking for a formula that adds months to a date and keeps the "x" day of the month. Meaning if I am adding 3 months from February 1st, 2021, it would return May 1st, 2021 vs. just adding 90 days to February 1st and returning May 2nd, 2021.
-
SUM and TODAY() function
Hello, I am building a formula to give me the SUM of the # of spots for dates called within the last 14 days. I was able to build a formula using the TODAY function: =SUMIF({# of Spots}, <TODAY(), AND({Date Calleds}, <TODAY())) However this formula returns a #INVALID DATA TYPE error. I added a number to the TODAY function,…
-
INDEX and MATCH with CHILDREN across two sheets.
Hi All, I'm having some trouble using INDEX and MATCH together to pull data from cells on one sheet across to another. I'm making an assumption that this is the best method however if I'm mistaken, please advise. I have a situation similar to the below where "MainName" is a Project Name and data beneath it is it's child.…
-
Index Match Max Date Formula
I have one table for shipping : And another table for Project Closeout: I would like to write a formula to auto-populate the max ship date for each project.
-
COLUMN AVERAGES WITH EMPTY CELLS
I'm trying to calculate our hospital ward census (COVID!!!), however on some days the column contains an empty cell /no value. I'm using the following formula, but to no avail. Getting an error message. Any help is greatly appreciated! =AVG(COLLECT($Count:$Count, $Count:$Count, ISNUMBER(@cell))) Stay safe.
-
Sum of Totals by area and Date
I am trying to set up a formula that does the following: If in Work Log A, the Plant is X and the date range is between 10-1-2020 and now, what is the total cost of this time range for this sheet? I also want to break down by monthly range: If in Work Log A the plant is X and the date range is between 1-1-2021 and…
-
Validate an email address in a contact list
Hello, We have a situation where we have two employees with the same name, "Test User", and they have the following email addresses: test.user@smartsheet.com test.user2@smartsheet.com (Please notice the "2" in this email.) We use a form to populate our sheet and the form user is required to type(paste) the email address…
-
Current user + formula in Contact List?
I'm trying to auto-assign an owner to a task based on the task type: The Owner column is a Contact List Column and the email addresses specified in the formula are associated with current user accounts (one being myself). But when I use a Current User filter, nothing shows! I can only assume that using a formula to fill a…