-
JOIN function
I am trying to JOIN 2 cells (same sheet) and it is returning an error: =JOIN([# Dosed]2:[N]2, " / ")
-
Join Collect formula for multiple values
Is it possible to use a Join / Collect formula, which references a multi-select dropdown list in one sheet to find and query an array of values? In Sheet 1, I'm querying a set of values however I can't seem to pull anything forward when multiple values are listed in the Client Name column. I'm trying to pull forward every…
-
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…
-
Issues adding an IF to COUNTIF
Hi everyone, I figured out how to use COUNTIF to count items within a column. =COUNTIF({Task Tracking - Phase}, "Initiation") + COUNTIF({Task Tracking - Phase}, "Planning") + COUNTIF({Task Tracking - Phase}, "Execution") + COUNTIF({Task Tracking - Phase}, "Close Out") I would like to create a formula that does this same…
