Feb 27 2022 05:56 PM
Hello again,
I'm not an advanced user but am familiar with bunch of excel formulas.
I want to dynamically retrieve all the values from a table field. After doing some research I have come to think that it's possible with array formulas.
However, I wasn't able to get it done.
Basically there is a table with employee names and I want to get all of their names based on a shift hour which are also listed in the table in a index match or xlookup friendly format. The table will be dynamic so I want my formula to be dynamic as well that's why I thought I'd need an array formula.
This is my formula but I'm getting an error. I'm thinking I'm doing a logical error with my cube formulas.
{=CUBEVALUE("ThisWorkbookDataModel",CUBEMEMBER("ThisWorkbookDataModel","[Regulated].[Shift].["&$M$2&"]"),CUBEMEMBER("ThisWorkbookDataModel","[Regulated].[Name]"))}
I've also tried index and match and xlookup but failed again.
{=INDEX(Regulated[Name],MATCH($M$2,Regulated[Shift],0))}
{=XLOOKUP($M$2,Regulated[Shift],Regulated[Name])}
I also want to note that there are blank cells in between employee names which is unavoidable due to the structure of the table requirements.
{=Regulated[Name]}
This simple formula retrieves all the employees from all the shifts. However, what I need it to do is exclude blank cells and be dynamic depending on the shift hour I select from a drop down list on M2.
I'm attaching a sample workbook to demonstrate what I need.
I want to express that the table is neatly formatted because it helps me visualize things better. Queue Assignment Mid Sheet is how the table formatted in default.
I'd really appreciate your assistance.
Thanks.
Feb 27 2022 06:41 PM
Solution@kheldar Well maybe here is another function for you to learn :) FILTER(). It is new with the introduction of dynamic arrays. There is also some other great new functions including UNIQUE, SEQUENCE, and LET to just name a couple.
=FILTER(Regulated[Name],Regulated[Shift]=TEXT($M$2,"hh:mm"),"none")
note I had to add the TEXT() to your M2 because the shift time is converted to text. I would recommend getting rid of that and just format that column to display in that format instead, but that is just me.
Mar 01 2022 06:53 AM - edited Mar 01 2022 06:54 AM
Can I bother you with something related with filter function? After researching a bit I've learnt that filter doesn't natively work with dynamic cell references #.
=XLOOKUP(A2#&"Meal Break",RawMMP[Name]&RawMMP[Break],RawMMP[Minutes])
This the alternative I'm using.
However,
This does not work:
=FILTER(RawMMP[Duration1],RawMMP[Name]=A2#,"na"),
Is it possible to make filter behave as such?
Mar 01 2022 08:22 AM
The problem is that both RawMMP[Name] and A2# are arrays. The first problem is that the result of such a comparison is ambiguous. If we assume that each element of the spilt array A2# is to be compared at a time, the result would be an array of overlapping filtered ranges, so still no use.
You either need to write a separate formula for each element of A2# and space them out, or you turn to more advance programming techniques.
= MAP(distinctName#,
LAMBDA(name,
TEXTJOIN(", ",,
FILTER(RawMMP[Duration1],RawMMP[Name]=name,"na")
)
)
)
This takes each element of A2# (I have assumed it to be a list of distinct names) and produces a filtered array. I used TEXTJOIN to turn the filter array result into a comma-separated list. MAP brings the lists together as an array of lists.
To put the result together as a composite list required advanced techniques which I have already discussed today within another post.
Mar 01 2022 08:40 AM
Mar 01 2022 09:00 AM
Mar 01 2022 09:02 AM
Feb 27 2022 06:41 PM
Solution@kheldar Well maybe here is another function for you to learn :) FILTER(). It is new with the introduction of dynamic arrays. There is also some other great new functions including UNIQUE, SEQUENCE, and LET to just name a couple.
=FILTER(Regulated[Name],Regulated[Shift]=TEXT($M$2,"hh:mm"),"none")
note I had to add the TEXT() to your M2 because the shift time is converted to text. I would recommend getting rid of that and just format that column to display in that format instead, but that is just me.