-
Use Contact List (formatted column) as data source to lookup info
I have spent several hours searching for an answer to what I think should be something simple to do. I have a sheet with information related to a contact, (address, supervisor, ph numbers etc). All I want to do is reference the name in the Contact List (formatted column) to pull up other information related to that person.…
-
Extract 10-digit string based on consistent lead-in digits
I have a number I'm trying to extract from a column and I can't figure out how. The number is *always* 10 digits, *always* starts with 50000, and can appear *anywhere* in the cell in Column A (start, middle or end). Column A has a mock-up of the type of data I'm working with. The Result column shows what I want to achieve:…
-
Formula involving Today() is returning the wrong number
I have a formula in my project plan summaries to keep track of how many days are remaining in a project. The formula is (=Finish3 - TODAY()). This formula works in other sheets. I have one sheet where it is not functioning properly. Finish3 is a date cell that is a rollup of the finish dates of all tasks in the project. It…
-
All Dates from Duration in a dropdown cell
Hi All, I am looking for a formula that take the data from the start and end dates columns and lists all in that duration in the "Dates" column. I am running 4 conference rooms within the center and I need to count the unique days that the aggregate center is used. For example: Conference Room 1 is active Jan 1 - Jan 3 = 3…
-
Search function in a form that searches the Data in the Smartsheet
New to Smartsheet. I have a form that collects information when we issue PPE to our employees. When the employee enters their employee number, I would like the form to search and display when sand if the employee has been issued PPE prior and when it was issued. This will allow us to request the old PPE (safety vests or…
-
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…