-
How to construct a multi sheet tracking log?
Disclaimer: i don't yet have sample sheets for this, because i've just started to try and conceptualize what i might need to do. Background: our company has a system that requires people be called in a specific order when someone else calls in sick and we need to offer overtime to someone else to fill in. When we get to…
-
INDEX MATCH MATCH where the cell is not blank
Hello, I have a unique situation where I cannot do a standard INDEX MATCH MATCH. We have a spreadsheet like the simplified version attached. I'm trying to grab the data where the name matches on the left and the date matches on the top. However, since there are multiples of the same name on the left, I only want to grab…
-
Calculate occurrences in last 12 months AND in current year
Hello! I am working on a dashboard that is focused on our tour program. I need a formula/function to show the amount of tours in the last 12 months and the amount of tours in the current year. Any help with this is greatly appreciated! Shannon
-
Hi I am trying to create a formula to give me a percentage complete in a sheet summary
Below is what I am using: =(COUNTIF(CABLE:CABLE, 1)) / COUNTIFS([TURN OVER]:[TURN OVER], "ECORE") this formula works and returns a % in my sheet summary that looks correct. When trying to get results from a different "turn over" I get incorrect results =(COUNTIF(CABLE:CABLE, 1)) / COUNTIFS([TURN OVER]:[TURN OVER], "AB") I…
-
Formula to analyse multi-select responses in a restricted drop down list
Hello, I have built a Form for a survey where users can select multiple responses from a drop down list. How do I write a formula to count number of repeats for each response from the multi-select list? For example: My restricted drop down list has - Car, Train, Airplane, Bike, Helicopter, Truck, Boat, RV. Users have to…
-
Checkboxes on one sheet to copy specific cells to another sheet
Okay, im new so just learning smarthsheet and ive been through vlookup (that didnt seem to work) and now IF (which did what i wanted at first but then ultimately was disappointing. This is the setup: Here's what im trying to do: I have an overall recommendations sheet that allows a client to pick and choose the subsequent…
-
Countifs from another smartsheet using a date range
I have tried 2 different formulas, and neither one matches the data which was previously on an excel spreadsheet: =COUNTIFS(CAR Log Range 1}, ">=01-JAN-2022", {CAR Log Range 1}, "<=31-JAN-2022") I get a value of 2, which should be 1 as per my excel spreadsheet =COUNTIFS(Date:Date, {CAR Log Range 1} >= DATE(2022, 1, 1),…
-
Formula to COUNTIFS criteria in one range matches but does not match in 2 other ranges
Hello: I am trying to write a formula which will count the number of cells in a range that return "No Match", but only if the cells in 2 other ranges DO NOT contain "No Match". In this case, I want to count the number of assets unmatched ONLY by S/N, excluding them if they contain "No Match" in 2 other ranges. I have tried…
-
Concatenate Existing Columns
I have an existing sheet with hundreds of lines of data. We recently decided we needed to combine two of the columns, but don't want to have to go through each line and combine them. Is there a formula I can use to copy the information from Column B into Column A with a space and comma in between? If I use…
-
Color code the cells
Hi All, I have a question regarding color coding the cells on Smartsheet. How can I color code column (Actual Delivery date) to highlight the following: Dark green : for when the Actual date is equale or less than Date committed Light Green : for when the Actual Date is after the date committed Red : for when Actual date…
