-
COLLECT formula year criteria confusion
Hi, I am trying to find the maximum value of values in Column A based on the year it is occurring as per dates listed in Column B. For e.g., finding the minimum value for all 2015 values in Column A. This is the formula I came up with but it is not working: =MAX(COLLECT([Column A]:[Column A], [Column B]:[Column ],…
-
Metrics Widget - Summarize from Many Sheets in a Workspace
I want to add a Metric widget to a Dashboard that totals the number of projects that I have in another workspace. Each schedule (sheet) in that workspace has standard summary fields that I use in Reporting. When a new schedule is dropped into the workspace, it is automatically picked up by all the reports that look at that…
-
INDEX/MATCH when the MATCH is a multi-select drop down column.
Hello! I have set up an Index/Match for the Secondary ID to be matched to the Sites Under Consideration. This works for the rows where there is only 1 selection in the Sites Under Consideration column. It gives a #No Match error for the rows with multiple Sites selected. I would like it to work and list all the secondary…
-
How can I get (on a separate metric sheet) the average SLA time within a given month?
I am trying to create a metric sheet to document how long response and acknowledgement times are taking per month for record keeping. I have a sheet that has an acknowledgement SLA column and a "date of request" (auto column for when the submission was created), and I'm looking to record the average for each month on a…
-
Using INDEX/MATCH to pull a date and CountIF not recognizing as a date
Hi, I'm using INDEX/MATCH to pull a date from one report into another based on the same ID number. If there is no match, an error message populates. =IFERROR(INDEX({Corporate CAPA Closed Effective Date}, MATCH([InfoCard Number]@row, {Corporate CAPA Closed InfoCard Number}, 0)), "CAPA Not Closed") Once the formula is…
-
Checkbox find duplicate in a column, do not check either box if either has a value in 2nd column
I am using a checkbox to find duplicate entries in the column name Email. I can up with this that worked, =IF(COUNTIF(Email:Email, Email@row) > 1, 1). Next, I am looking for both duplicates to not have a value in the Using Generic Account field and then check both boxes. Do not check either box if one of the two…
-
Metrics Sheet Doubling values
Hello, I am trying to solve have a cross-reference sheet problem that is doubling my values in my metric sheet. I am using =sum(children()) in my source sheet to sum the values under each parent/child row. When I reference the destination sheet for metrics it doubles the value because I think I am referencing to sum all…
-
Multiple Checkbox's for sheet summary into a dashboard.
Good afternoon, I am looking to see if this is possible. I am looking to have a dashboard automatically populate with a new row when another is "complete". Which I am able to accomplish with automation tasks. The issue is building the dashboard that has multiple columns of checkbox's. Please see the photos below for an…
-
Help needed for SUMIF, Date Range
Hello I am having trouble with the below formula to return date based on a date range and getting a return of #invalid operation. I checked this out in the community but not able to find a resolution. =SUMIFS({All PM PO Tracker-Assigned To}, "Project Manager", {All PM PO Tracker- Change Order-Submitted}, "Invoiced",…
-
Formula that counts how many rows have a date in the past
Hi! I need help with creating a formula that will count how many rows in my column called End Date have dates in the past. I've tried the following and it gives an "unparseable" error: =COUNTIFS([End Date]:[End Date], "<=" & TODAY()) Thanks in advance for your help!