Spill Array Syntax

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so. Cross-posts are allowed but you must provide a link to your posts on other sites.
For new members: you will not be able to post a full URL, so remove the https://www. section and add one or two breaks to the rest of the address. A Moderator or Admin will then fix the link for you.

Please see Forum Rule #7 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important: https://excelguru.ca/a-message-to-forum-cross-posters/

(Note: this requirement is not optional. No help to be offered until you provide a link or, for members with fewer than 10 posts, a comment telling us where else you have posted this query.)

Hello,

Sorry about that. I wasn't sure if the addition to the post on Mr. Excel was being considered based on the response I got but I totally understand needing to know about it. The link is here: https://www.mrexcel.com/board/thread...tance.1270598/

Thanks.

If you still need help, please attach a sample workbook - we can't test anything using screenshots.

Please update your forum profile from 2010 to 365.

Thanks for attaching your file, that's very helpful.

The description in your post is a bit different than what I see in the file, but I think from context I get what you want to do.

This hinges on being able to make the columns to display dynamic. I don't know if you can do that with CHOOSECOLS. I'll have to poke into that a bit.

Hello,

Yes, you can use ChooseCols() which is what I've done and pointed to the "columns" of the 3 items selected (C13, C15, C17 on summary tab). But maybe choosecols() isn't what is best to use? I did a "let" statement once where the data was one row per data and was able to summarize to get cases based on the groups I wanted but it was hard coded and not dynamic. I also don't know how to work the filter into the "let" statement.

To recap, the filter for the items on the summary page of D4:D8, H4:H8 and K4:K8 is working as I would like. The dynamic choosecols() is working. The only thing that I can't figure out is how to summarize the data and not just show the single record. Not sure if the "notes" worksheet is of any assistance to you but I outline the info there.

Thanks so much for any assistance you can provide and please don't hesitate to ask me anything. Also, don't hesitate to suggest another approach if it works better.

Is this what you want?

Please Login or Register  to view this content.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

As a tip, the formula could be cleaned up a bit by first defining a custom function for the "include" criteria to simply return a pre-constructed vector of 1's the same length as the fields being evaluated when "All" is selected. For example:

Please Login or Register  to view this content.

Also, XMATCH was used with TOROW on the selected columns range to return the column numbers for CHOOSECOLS dynamically. This eliminates the need for helper columns F & K on the Summary worksheet. See attached...