1

I'm trying out the new functionality of Office 365's Insider versions of Microsoft Excel, dynamic arrays, and was trying to make something I've been wanting to do for some time in a simple way.

In essence, I would like to know if it is possible to either use each one of a dynamic array's values as an input to create a matrix of dynamic arrays, or even just plain creating the matrix below (table 3) altogether with just one array.

Table 1 and 2 below are structured tables.

Table 1 - Clients:

| Client | Client Name |
|--------|-------------|
| C001   | Client 1    |
| C002   | Client 2    |
| C003   | Client 3    |

Table 2 - Documents:

| Document | Doc Description | Related Client |
|----------|-----------------|----------------|
| D001     | Doc 1           | C002           |
| D002     | Doc 2           | C002           |
| D003     | Doc 3           | C001           |
| D004     | Doc 4           | C002           |
| D005     | Doc 5           | C001           |

Table 3 - Intended dynamic table:

| Dynamic Array of "Clients" | Client 1 (*1) | Client 2 | Client 3 |
|----------------------------|---------------|----------|----------|
| Dyn. Arrays of "Documents" | D003 (*2)     | D001     | N/A      |
|                            | D005          | D002     |          |
|                            |               | D004     |          |

In the table above (*1) would be a Dynamic Array created with something like:

=TRANSPOSE(Table1[Client])

And (*2) would be a Dynamic Array created with something like:

=FILTER(Table2[Document], Table2[Related Client]={Client}, "N/A"}

The issue is that I haven't been able to find how to create all the Dynamic Arrays of "Documents" in a way that they automatically grow in number along with the "Clients" dynamic array".

I.e. I can use simple relative cell references for {Client}, but when the "Clients" dynamic array changes (increases) that won't dynamically adjust the scope of the Document dynamic arrays. I thought that using a dynamic array reference of suffice, but it doesn't work as well ({Client}#).

I also tried to do this in a structured table, but sadly dynamic arrays don't work in those.

Is this possible?

Edit: I'm looking for an answer using dynamic arrays to their extent, please.

1

Your issue can be solved by using a Helper Column along side of Table 2:

enter image description here

How it Works:

  • Enter this Formula in Cell O40 and fill it down.

    =INDEX(M$34:M$36,MATCH(N40,L$34:L$36,0))
    
  • In Cell M48 enter this Array (CSE) Formula, finish with Ctrl+Shift+Enter and fill across Table.

    {=IFERROR(INDEX($M$40:$M$44, SMALL(IF((M$47=$O$40:$O$44), MATCH(ROW($O$40:$O$44), ROW($O$40:$O$44)), ""),ROWS($A$1:A1))),"")}
    

N.B.

  • You may replace Blank with Not Aval. text message in last part of an Array formula,
    ROWS($A$1:A1))),""), and it should written like, ROWS($A$1:A1))),"Not Aval.").

Adjust cell references in the Formula as needed.

  • I'm sorry, but this is not the answer I'm looking for. You're using array formulas, not dynamic arrays of the most recent Excel engine versions (example: link ). The particular issue of my question is how to make the dynamic arrays also span dynamically in reference to a base dynamic array. Also, I believe your formulas do what I've already done in the question, using dynamic arrays. – acseven Apr 22 at 13:02
  • @acseven,, Check this IF((M$47=$O$40:$O$44) in Array formula which filter/extracts multiple rows based on Criteria in Cell M, N & O 47 and to associate data I've created Helper column O40:O44. I'm using 2013 & 2016 the link shows FILTER for Office 365 is like Google Spreadsheet or I can say Advance Filter in Excel or similar to SUMPRODUCT mechanism ,, write me what you is missing or you want to add on with my Answer ! – Rajesh S Apr 23 at 4:49
  • Hi - again, this is unrelated to my question. I'm not looking for how to create array formulas such as those, I'm looking to know if there is a way to solve an issue specifically using with dynamic arrays. If you don't have the latest Office 365 (2019) insider's edition you don't have access to these functions. – acseven Apr 23 at 8:51
  • @acseven,, I think that you are looking for Dynamic Named Range,, which automatically expands when gets new entry !! – Rajesh S Apr 25 at 8:48
  • No, that's a different thing as well. Cheers – acseven Apr 26 at 10:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.