# dynamic array

1. ### Use a Dynamic Range with Dates as a Criteria in FILTER Function

So I manage to create a dynamic range of dates with all the 1st of month between a period of time. What I want to do, is sum all the expenses of respective month from another table. That result should be another dynamic range of the same size as the one with the dates in order to create the...
2. ### There has to be a better way...

For weeks I've been trying to find a better way to make a Wind Rose Chart or a Spiral Bar Chart to keep track of my Achievements in Steam. I tried to ask AI-aided Formula Editor but it's either not working or I'm not asking the right questions. I'm trying to find a better way to write the...
3. ### Dynamic Average of top values in specific ranges

Hi everyone. Im trying to find the average of the top values of several- non continued ranges. see image for reference: What I need is being able to average the TOP value of range B4:B15 with the TOP (the ones in green) value of B17:28 and so on for the next following cells that are not in...
4. ### Return multiple columns for each row with Dynamic Array INDEX/MATCH

Hi, I'm wondering what the best way is to return all columns (T and U) in the following setup: I have tried using BYROW/LAMBDA to no avail. Perhaps there is a better function than INDEX/MATCH for this?
5. ### Comma Separated Array List

I've just hit a wall here and my brain refuses to bring this information up. I have a cell with a series of room names in it, separated by commas: Master Bedroom, Master Bathroom, Laundry Room, Stairs, etc. Call this A1 In "D1" I'd like to place a formula that will take those room names and...
6. ### Lookup value using two separate dynamic ranges.

I have created two custom formulas that each return a two dimensional array that result in dynamic arrays that spill to adjacent fields. The following examples are a mockup of my actual formulas as they are large. Dynamic array 1 is on it's own sheet called "Orders" and looks like this: Custom...
7. ### Connecting Dynamic Table with Manual Static Data in Sheets

Hi All, first time poster, long time lurker! Problem: Gym churn report. Client details are imported from a CRM who match criteria (not attended a class for 30 days). The Trainers then manually add if they contacted said member and add notes. When this list is refreshed and client data gets...
8. ### Using Index with dynamic multiple columns

Hi Community, is there a way to get INDEX to work with multi-column select dynamically. This Works: INDEX(Table,,{3,5,9}) This Throws an Error: INDEX(TABLE,,{COL_A,COL_D,COL_C}) EDIT: Just an FYI; this also works: INDEX(TABLE,,COL_D)
9. ### Using an array to dimension controls on a form to be added at runtime

have the following code in my user form which uses a class module (not shown). It all works great, but I want to add the buttons dynamically according to a list in my worksheet and don't know how to use an array to dimension them. At the top of my user form I Dim the events references: Dim cbe1...
10. ### Spill dynamic array across multiple columns with a defined depth

Hello Is it possible to distribute a dynamic array into multiple columns? For example, consider an array with 22 values, and you want to spill the values in sequence across 6 columns with a maximum row count of 4: In this example a have put numbers in sequence for the sake of simplicity, but...
11. ### VBA Use dynamic array to create custom report

Please assist if you can! I have an exported Excel report, each row is a new customer, but the rows stop at 137. There are about 250 columns with customer details, but only 5 columns belong to 1 customer in each row. My boss wants me to format this report to reflect each customer in individual...
12. ### Multiple a dynamic Range based on conditions

Hi everyone, I have a bit of convoluted question regarding multiplying dynamic amounts based on date and time conditions. I have tried a fair few different formulas including sumproduct, vlookup and ifs - but nothing gives the right answer. This is quite a large sheet so I have only copied...
13. ### How to Average Only Non-Zero Values Using AverageIfs with Index/Match

Here is my current formula: AVERAGEIFS ( INDEX ( \$H\$10:\$X\$505, , MATCH ( [SKU], \$H\$10:\$X\$10, 0)), \$E\$10:\$E\$505, [City], \$F\$10:\$F\$505, [Store Name]) I am trying to summarize the average prices (H11-X505) by product (header cells H10-X10), city and store (Columns E-F) in a separate table using...
14. ### Sumifs using unique and filter functions

Hi Everyone, I have attached a spreadsheet and highlighted in yellow the column K I would need to be filled with a formula (spill formula only so that it is completely automated). Column I and J: This is a spill formula using Unique, Filter and Choose function. Column K: I would need a spill...
15. ### UNIQUE and CHOOSE for non-adjacent columns

Hi Everyone, I have an issue with a formula and I have been told we have all the experts in the world to answer to this question. I am trying to get a list of unique values for 2 non-adjacent columns. I have tried the formula below but it doesn't work. It only spills the result for one single...
16. ### Is there a better replacement for this formula?

Hello, I'm betting there is a better way to run this formula that references a block of dates by month, and returns the table rows based on the short date related to the selected month. In the example below, I have a dynamic array showcasing the rows of data from a table based on the month...
17. ### Filter table rows for partial string and return column headers as dynamic array

Hello, Given the table (Table3) representing the 1:1 slots between 2 participants with the following data: JKLMNOPQRSTU2TIMESLOTGroup 1Group 2Group 3Group 4Group 5Group 6ParticipantSlots to participateGroup309h00-09h15X8_1A - X8_BX8_1E - X8_2BX8_1B - X8_5X8_1C - X8_3X8_1D - X8_AX8_2A -...
18. ### Aggregating columns of dynamic 2D Array

Hello, Given the table (Table3) representing the 1:1 slots between 2 participants with the following data: TIMESLOTGroup 1Group 2Group 3Group 4Group 5Group 609h00-09h15X8_1A - X8_BX8_1E - X8_2BX8_1B - X8_5X8_1C - X8_3X8_1D - X8_AX8_2A - X8_609h15-09h30X8_1A - X8_4X8_1C - X8_2AX8_6 - X8_3X8_2C...
19. ### Utilizing Dynamic Arrays for Compound Annual Growth Rate

Dear Members, I need assistance in creating a CAGR array from periodic returns vector. I wish to create a Dynamic Array holding only calculated CAGR (compound annual growth rate) values derived from periodical return on investment vector. This is easily performed in standard Excel but...
20. ### Sum range totals in a date range where range does not match criteria

Hi everyone, I have been searching for a way to calculate a range of cells that meet two date criteria, however the problem I have is figuring out how to use SUMPRODUCT or similar as the sum range is dynamic. My goal is that users will be able to enter a range of figures into an 'Hours' Column...

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back