-
Distinct CountIFS Statement
I need a little assistance... I was able to get the formula to partially work using =count(distinct but, it was not taking the name cell into account and returned the same number in each row. I need a distinct count of PrAbv based on P Name. I got "#inccorect argument" when I changed count to countifs. I'm sure it's…
-
Average symbols for growing number of columns
Hello all, I know this is likely possible, but I've been struggling to think of the most efficient process. Below is a sheet I'm working on, where trainers enter progress for a trainee in a given "soft skill." These trainer columns will be added automatically on a daily basis via an outside Power Automate flow, while the…
-
Index Match References Disappearing
I am experiencing glitchiness around programmed index match references disappearing. The sheets I have linked will function fine for a period of time and then we will notice the data begins to simply not populate in the receiving sheet. When I look at the index match reference, its gone and I have to remap it. Our formula…
-
Streamline Sheet Reference Manager
I just went through an update where I needed to manually add 3 new sheet references to 26 Stakeholder Registers (78 total). I wish that after you click Insert Reference, the Sheet Reference Manager dialog box would remain open.
-
Date (mm/dd/yyyy) from Week number
I am trying to get the date displayed from a week number. For instance this week is the 27th week of the year. I am trying to get it to display 07/05/2021 (the first day in the week). I was thinking of converting the week to day of the year (i.e. 27 * 7 = 189) and using the 189 to convert to a date, but I can't figure out…
-
Cross-Referencing Assigned To Error
I am building out a roll-up sheet and have everything working nicely, with the exception of a single formula. The part of this sheet I'm having trouble with is in the image below. What I am trying to do is have this sheet look at the person named in the column "Primary", then check a couple other sheets to see how many…
-
Is there a way to conditionally copy the value of one cell to another?
I am trying to create a modified auto-numbering scheme where the numbering is based on a parent/child relationship. I think I am on the right track, but have hit a roadblock. My end result should be a calculated ID that is not overwritten when IDCalc changes. I would like the value of IDCalc to populate in the ID column…
-
How to automatically check a parent row when children are checked?
Hi, I have 3 level parent-children rows. I also have a column with a checkbox. Is there a formula or automation that would check the parent row if all children are checked?
-
Join-Distinct-Collect Formula Returning #INVALID DATA TYPE
I'm using a Join Distinct Collect formula to return a list of product numbers from a detailed schedule (formula below). I use this and other formulas to populate a weekly summary sheet. =JOIN(DISTINCT(COLLECT({Schedule Item}, {Schedule Make Date}, =Date@row, {Schedule Room Name}, =RmName@row)), ", ") Whenever the schedule…
-
HAS cross-sheet reference & Invalid Column error
I am trying to use HAS in a cross-sheet reference. I have a drop down form where the user selects a Strategic Priority and it goes into Sheet A below. In Sheet B, I need a formula to pull the text of the most recent selection. Here is the formula I am using in Sheet B, where Range 1 is the "Latest" column and Range 2 is…