-
Need formula to highlight row if same site # was entered within the week
Hi, My team currently works off a Smartsheet that tracks task e-mails we receive. Recently, we've been receiving a lot of duplicate items in our e-mail that we categorize with Task Subcategory "(TE) Critical Date Entry" on our tracker. Each e-mail corresponds to a specific site with a specific Location #. Basically, I'd…
-
Displaying all unique values on a new sheet from columns on a reference sheet - Help
We have a form set up for collecting information from scientists performing runs by different projects. We need to be able to supply material lot information to clients separately by project numbers. I tried to use the following forums to perform the index/collect/distinct formulas which didn't work for me when referencing…
-
JOIN function
I am trying to JOIN 2 cells (same sheet) and it is returning an error: =JOIN([# Dosed]2:[N]2, " / ")
-
Child Row Formula to Reference Parent Row
Hello! I'm trying to figure out a formula that will always reference the Parent Row. I need all the cells in the Practice Name column to reference the parent row above it. I'm assuming I can't make this a column formula, otherwise the parent row I need to reference will be referencing the parent row above that. So for…
-
Idex/Match is refusing to match against any "Project ID" that does not start with "SM"
I cannot for the life of me figure out why an Index/Match between a project Metadata Sheet and the project Intake Sheet refuses to find any matches unless the Project ID has "SM" in it. This was originally the "Project Management Office" template from Smartsheet, but has since been revised. My formula on the Metadata sheet…
-
Sumif/s, VLookup or something else?
I'm in need of some formula help. I need a formula for the Males2 and Females2 columns on sheet 1, it should total up all the males/females from Sheet 2 and return that value to sheet 1. For example the result on sheet 1 for Number 2 should be 7 males and 5 females. The data is on two different sheets which shouldn't…
-
Cross reference sheet formula with date limits
I'm trying to count the number of complaints for Family Medicine within the last 30 days on a metric sheet referencing the source sheet Complaint Tracker. Here is the formula I think should work: =COUNTIFS({PR - Complaint/Grievance Data Collection Range 2}, "Complaint", {PR - Complaint/Grievance Data Collection Range 1},…
-
Using the Work Day Formula to Calculate Due Date
Hi All, I am trying to generate a formula that will calculate a due date (MM/DD/YYYY) based on working days of the month. My problem is that our team uses WD1, WD2, WD3, etc, to denote a due date, not dates in a date format. For example, WD1 in November would be 11/1/2024 and WD2 would be 11/4/2024. Does anyone have ideas…
-
Automate Copy/Paste Special in same sheet or to another
We have "live" metrics collecting data from multiple departmental sheets, and display them on a dashboard. Works beautifully. The challenge is, we need to save each metric into an historical sheet, which means we need to store the numbers as static numbers. Copy/Paste Special accomplishes this — but it has to be done…
-
weighted task %, % complete by time
Hi, I'm having trouble wrapping my head around how to produce a formula for my scenario. We are considering each task (hierarchy 1) a percentage of the overall project. For example, research is 5% of the project, production is 25%, etc. For simplicity sake, I would like to report % complete based off of time elapsed for…
