-
Having trouble with Index and Small formula...?
Hi, I'm trying to find the smallest (or oldest) date in a column and then return a cell value from the same row of a different column. Below is my current formula. I want to return the "Lumber Type" with the smallest date from the "Last Cycle count Date" column. =INDEX([Lumber Type]1:[Lumber Type]35, MATCH(SMALL([Last…
-
% Complete Question
Hi, I am fairly new to SmartSheet and been looking for help, but can't seem to find what I am looking for, I have tried a similar formula I saw on here, but I can't get it to work. I need a simple formula to show how far along a project is in %, based on dates or no of days. I have fudged it for now with a traditional…
-
AVG error message (with formula issues as well)
I'm trying to calculate the average score (1 to 5) of a column, but keep getting the DIVIDE BY ZERO message. The column with the scores is calculated via a formula from a column formatted with stars (from a survey form). So four stars from the survey = 4. But I noticed that all the results have a ' in front of them (e.g.…
-
Join values from multiple select column
Hi I am looking for a formula that joins the values selected in a multi-select column as per the example below:
-
Vlookup - copy a date to another sheet
Hey, I would like to use a vlookup to copy a due date to another sheet. The vlookup works in other cases, just with the date it doesn't and I get #INVALID COLUMN VALUE as a result. I know it has to do with the date itself but I am unable to figure this out myself :-) - can someone help? The formula:…
-
I need to count the number of times a column contains specific text
I have a column that is a drop down where multiple selections can be made, and I need to count how many times each selection is selected in the column. an example of the items in the drop down are: A+ PenTest+ Project+ Server+ Security+ Tech+ CSIS CIOS I need to be able to count how many of each are selected. Formula of…
-
pull information if columns match
Hi smartsheet community! I would like to pull a value into a sheet from another sheet if the email address matches sheet 1: source sheet if 'email address' on sheet 1 matches 'created by' on sheet 2, I would like to pull the field 'Name' into sheet 2 =IF({2024 L&D Budget Source Range 1} = [Created By]@row, {2024 L&D Budget…
-
Help with status column formula
Hello, I have created a formula to show the status as not started, in progress or complete when the %complete column is changed. This is the formula =IF(OR(ISBLANK([Due Date]@row)), “”, IF([% Complete]@row = 1, “Complete”, IF(AND([Due Date]@row < TODAY()), “Overdue”, IF([%Complete]@row = “0”, “Not Started”,…
-
The column formula syntax isn't quite right
Hello, I am getting the error that the column formula syntax isn't working. I have used the @row function and it works in each cell, it just can't be converted to a column formula. Any help is appreciated. This is for the "P/D PMI PMLast" column. =IF($[P/D PMI PM]@row = "", "", RIGHT($[P/D PMI PM]@row, LEN($[P/D PMI…
-
Using IF/THEN to Create a Password
I have a sheet to track password-protected documents and currently have a formula to automatically create a password. However, I would like it to create a password only if the document will be returned via email. The formula for the password is: =RIGHT(ID@row, 5) + LEFT([Last Name]@row, 4) + [Year of birth]@row. How would…