-
Collect most recent date from another sheet based on certain criteria
Hello I have a fleet maintenance log that has invoices per vehicle with a column that is a drop-down with multiple values based on the service provided. I am trying to make a sheet that would be a quick reference to see the most recent service per vehicle per category. Being multiple services will but under a category I…
-
How to count a range of different names from a range of dates?
Sheet A has a week ending date column (always Sunday). Sheet A also has a column called Manpower where I am trying to write a formula. Looks like this, Sheet B Has an install date and an Installers Name. Looks like this, The formula I am trying to write would find all dates from Sheet B that fall within the week ending…
-
TODAY function and status
I'm working a formula for a RYG column based on two major sets of data: Status and Quarter Start Status and Quarter End Q Start and Q End are the dates of the quarter and they are on the sheet as helper columns. For example: If Quarter starts in the next 80-120 days: blank status - Yellow Status is Product discovery,…
-
Exclude a range of values from a source range of values
Hey all, I've got Sheet 1 - List 1 that is an intake sheet with a range of opportunities (each identified by a unique opportunity number with a set number of digits) exported from our CRM program. This sheet is automatically updated whenever a seller puts a new opportunity into the CRM. I am not allowed to make changes to…
-
IF formula to compare due dates to show completion schedule
Hello all! I have a formula that is stumping me - I am looking to compare a target due date versus the actual completion date. I have a formula that is partially working, but am running into issues when I use the term "ongoing" on the target due date column (see Project 4 in the below example). This is what I'm trying to…
-
Formulaic Help / Cross-Sheet References
Bit of a complex one: Let's say I have a sheet named "Additions To Scanning Workflow" (with a select number of users) that includes 13 columns of data, one column ("Ready for Digitization Dept.") that's a checkbox field. I want the select number of users to be able to fill out this sheet and when the "Ready for…
-
How do I add age range
I am trying to take a list of all employees that includes their info like department, name, and age and then create a age range (like 16-24, 25-34, etc...) so I can then count how many employees fall under each age range. I have tried two ways and neither work. FIRST ATTEMPT: -The first and i think easiest is to have a…
-
Restricting a formula to a certain timeframe.
Hi There, I have a Smartsheet and Form that we use to calculate how long a person was checked into a function to account for down time. Since Smartsheet is not very good with calculating time it took some creativity go get a formula to match up 2 different rows with the same information to calculate the amount of time…
-
Help with IF formula
Hi there! Does anyone know if there is possible to extract a portion of a text in a cell? (to be specific, I only need to pull the first 5 characters from the left) I currently have a formula to "map" a list of values (using IF function), but now we have added this long list of alphanumeric codes into the "ComponentID"…
-
Copy row automation from a formula based column
I have 3 intake sheets for a multi tier blueprint, I am using data shuttle to bring in all of my clients, program and project to one master sheet and from that master sheet, I have 3 formula based check box columns to track if the client, program or project exists or doesn't exist. If it doesn't exist I want to copy the…
