Have the following sheet which I use as a form of LookUp table for dynamic Data Validation in other sheets.
Where A3 is calling this function: TRANSPOSE(EVEONLINE.CATEGORY(9,TRUE).GROUPS)
and A4 all the way to the end (right) of the sheet has EVEONLINE.GROUP(A$3.id;TRUE).TYPES, for some reason the reference A$3# does not work in the above function call, which would have been easier than having to drag the formula across the width of the sheet.
My question is, is there a way to filter out certains results from the array resolved from the funtion in A3? I’m specifically interested in removing the 6 groups which does not have any manufacturable BPs, such as Clone, Capsule, Proximity Drone etc., from the array.
I have tried to use the FILTER function but it does not work within Data Validation which is where I would like to have used it.
The group function doesn’t accept arrays as the input as doing so, i.e. supporting array output (like assets/transactions etc), breaks display of nested arrays, even if the function only gets a single ID as its input.
I’ve had a similar question asked in the discord, and the “cleanest” solution is to add a third parameter to the .CATEGORY function to have it display the .GROUPS with the .TYPES info as your logic is doing, and thus you’d be able to get the data. There are some categories where the resolution time is intense and sometimes times out, so maybe it’ll not solve anything. But I’ll at least make a ticket for it.
For filtering out certain results from Data Validation I’m not really sure how that would be solved, I’ll have to make a ticket to try and recreate the combination of data validation and tinker with it, hopefully someone can see this and give you an answer as it’s very likely going to be a while until I have time to check it out personally.
I think I’ve found a workaround to what I was trying to do. Well in regards to filtering out the unwanted Blueprint Groups which does not have any blueprints (or blueprints does not exist in-game). I was first looking into using the function Filter(Array, Inqlude, [if emty)], like i mentioned in OP, but I’d have to use multiple nested Filter functions in order to achieve my goal.
Searching around the web for other ways that I could possible achieve the same result with less nesting of functions and/or least length of the entire formula, I came across DROP Function (Microsoft Support Page) and started to tinker with it and came up with the idea of using the DROP(Array, Rows, [Columns]) to segregate the larger array into smaller ones but excluding the unwanted data, I think I ended with 6 segments. Now I just needed to stitch the array back together and for this I found the HSTACK(Array1, Array2, ...)(Microsoft Support Page)
Using DROP and HSTACK functions in combination with each other I ended up with the following (lengthy) formula: =HSTACK(DROP(DROP(TOROW(EVEONLINE.CATEGORY(9; TRUE).GROUPS);;1);;-199);DROP(DROP(TOROW(EVEONLINE.CATEGORY(9; TRUE).GROUPS);;6);;-153);DROP(DROP(TOROW(EVEONLINE.CATEGORY(9; TRUE).GROUPS);;52);;-129);DROP(DROP(TOROW(EVEONLINE.CATEGORY(9; TRUE).GROUPS);;76);;-92);DROP(DROP(TOROW(EVEONLINE.CATEGORY(9; TRUE).GROUPS);;113);;-77);DROP(DROP(TOROW(EVEONLINE.CATEGORY(9; TRUE).GROUPS);;128);;-1))
On a side note, it would be nice if the .GROUP function could work with an array (reference cell with a # added on).
