-
Multiple Criteria and Date Range Formula
Hi, I am trying to create a formula that will reference another sheet, look at two criteria within the same column and a date range between two columns. The following works to get the two criteria from a column but I cant seem to be able to add the date range to it. =COUNTIFS({Sheet - Status}, OR(CONTAINS("Complete",…
-
Index Collect Based on Another Cell
I have a drill down sheet in which I need to collect the plan values from the master. Master Drill The Drill sheet contains SRC ID, Month & Year this can pinpoint a single value in the Master sheet. If I create a range containing only the Jan column it works: =INDEX(COLLECT({Master Range Jan Column}, {Master Range SRC ID},…
-
Sum values from multiple columns when associated checkbox is ticked
Hi I am trying to use the IF formula to sum multiple column values if an associated column checkbox is ticked - basically duplicating the formula below to the other checkbox field and specific columns. It tried the "AND" function - without luck. Any expert able to assist? Thank you
-
Conditional formatting for reoccurring tasks
Hello, I am trying to design a task tracking smartsheet for tasks of varying reoccurrence, i.e., monthly, quarterly and annually. In this sheet, the idea is to use conditional formatting as a means to identify when something is past due (in the past, red), upcoming (in the next 60 days, yellow), completed (green) or in the…
-
Return the cell with a value from 3 cells
Good morning all, I can't get my head around this one so wanted to see if anybody can help. I have 3 columns: Downtime Start Time OOO Start Time Manual Downtime Start Time I would like a formula in a different column that searches all 3 of these cells and returns the first one that isn't blank. Is this possible? Thank you…
-
COUNTIFS using today's month
Hi I'm currently trying to count the number of jobs, matching a specific work type when the received date matches today's month. I am referenced another sheet (Job Log) also. The formular I am currently using is: =COUNTIFS({Job Log l Job Type}, [Job Type]@row, {Job Log l Office}, "Birmingham", [{Job Log l Date Received},…
-
Find first non-blank cell with INDEX/MATCH
Hi guys! I'm pulling 2 columns, each with multiples of the same value, and some rows have blanks in the cells until the reference sheet is updated with said information. My INDEX/MATCH works great, except there it pulls the blank values, and thereby reducing the accuracy of my sheet. The formula is as follows;…
-
Autofill contact details
Hello all, I'm trying to create a formula to "Autofill" contact (Name, phone number & Email), What is the smartest, fastest way to create a formula for over 500 contacts? Thank you
-
Moving data from client report to my template
Hi - I'm importing client data to a template and I am using INDEX and MATCH formula =INDEX({Crane Range 6}, MATCH([Contra Firm / Sponsor Acct #]@row, {Crane Range 2}, 0)) This works well for one column at a time. I want to capture multiple columns at once so I'm not copying first name, middle name, last name, street…
-
How do I display counts of all of the dropdown options used on one sheet and possibly multiple?
Long story short, I'm building a system to track pending legislation, and for each bill that posts, team members are assigned it to one (or more) categories in a CATEGORY dropdown column. I'm working on a dashboard of high-level views, and I'm trying to figure out the most efficient way to roll-up and display how many (and…