-
Subtract list of items from Inventory
Hi, I have a Inventory Sheet with list of items and also the quantity of each item. I also have products that use various items from the Inventory list, for example: Product #1 = 2 screws, 1 sign, and 2 hooks. And the way I have set it up now, every time when I sell Product #1 I have to manually search up each item and…
-
Populate a text value based on a date range
Hi All, I'm fairly new to Smartsheet and I'm trying to set a text value based on a date range. Reading through all the responses it seems possible but a little messy. All I'm trying to do is automatically populate a Quarter value based on custom ranges. No cross sheet references, all in the same sheet. When I only use two…
-
Formula to check the cell for 4-5 text strings
I have a condition where in the formula should check the text in CELL A and return value based on the assigned values and should leave the formula cell BLANK if none of the condition is satisfied. I tried it through spreadsheet and the formula is: IF(OR(A1= "Name A", A1="Name B",A1="Name C",A1="Name D",A1="Name E",A1="Name…
-
How would I return a specific status based on differences in multiple other cell columns?
I am attempting to create an automatic status update that is driven by different options selected in multiple other cell columns. For example: I want my Status column to indicate "Assessment" if another column regarding the assessment is marked as "Yes". However, if another column regarding a meeting is marked as "Yes," I…
-
Formula for totalling specific options
hello! I have a sheet for a menu of options. We are building kits of different swag items and the sheet has 5 different options with different price breaks. I'd like a sales rep to be able to go in and select different options then see a kit price. I was thinking I could add a check box column then pull a report on that…
-
At Risk Flag triggered by upcoming due date and/or past due, and % complete less than 100%
Hi, I'm looking for formula help with the at risk flag. Goal: If target finish date is within 7 days OR past due, and % complete is less than 100%, then activate flag. when % complete equals 100%, flag can deactivate. =IF(AND([Target Finish Date]@row <= TODAY(7), [Target Finish Date]@row >= TODAY(), Status@row <>…
-
Use date and status to define RYG balls
Hey all, Trying to make a simple task management sheet, using today's date and the task status to define a Red, Yellow, Green or Grey ball. If end date is today or later, AND status is not complete or cancelled, green If end date is today -1, AND status is not complete or cancelled, yellow If end date is today -2 or…
-
Trying to pull data into a cell from one column based on criteria from another column
Hello everyone, I've built a project schedule and I'm trying to pull only the task that's "In Progress" into the parent row of that phase. So basically my formula needs to look at the children in the status row, find the one that states "In Progress" and pull the data in the Task column from that same row into another…
-
How do I check for duplicates within several columns and check a box
Hi All, I'm making a grid for staffing events. Basically each event can have up to 3 staff members. I want a formula that will check for if there are multiple events on the same date and if a staff member is accidentally double booked. If they are double booked, to check the checkbox so I can run some conditional…
-
Contact List using DataMesh & Concatenate
Hi, I have added 2 columns to a data sheet, one for Analyst Name and one for the Analyst Manager. I have brought in the email addresses of both of these by DataMesh. I want to be able to apply a current user filter to a certain column which would enable the Analyst and their Manager to only see the rows that are applicable…