dynamic arrays

  1. T

    How to combine dynamic arrays vertically

    Hello, I'm trying to determine if it's possible to combine multiple arrays of dynamic data into one vertical array. I've tried using a SORTBY function in Cell A7 but it seems to try and combine the arrays horizontally. Is there a way to combine dynamic data vertically...
  2. L

    Dynamic Arrays - Count Days

    Hi!! I am traying to sum all the days that has elapsed between the origin date and a fixed given date when some conditions happen to occur. I copy below a sample. DATE (A) SOLUTION (B) 05/05/2021 10/05/2021 10/05/2021 10/07/2021 20/05/2021 20/07/2021 20/05/2021 30/05/2021 25/05/2021...
  3. L

    Dynamic Arrays - Top 5 values

    Hi! I am having some difficulties with my dynamic arrays I need to create two different dynamic arrays, in which I extract the Top 5 values aggregated by "Case". In the first scenario, I would need to obtain the Top 5 values when...
  4. N

    Dynamic array to replace pivot table (sorting problem)

    Hello Excel comunity, I started playing with dynamic arrays to replace PivotTables. It s working well but I am struggling to order the data in the way I need. I wonder if is possible to make this Dynamic Array table without using the Auxiliar Table. Here is an example: Formulas...
  5. A

    Dynamic Formula Based on latest column added (Budget Vs actual)

    Hello experts, I need your help to know what formula should I apply to get dynamic results. In the attached spreadsheet I have sample data of my monthly report, where we have monthly actual column and remaining month forecast (M05 to M12). I also have monthly budget and monthly forecast...
  6. L

    SUMPRODUCT inside Dynamic Array with CHOOSE - impossible?

    Hi! So I want to make a Dynamic array with Choose, where one column will be the unique clients and in the second column I want the SUMPRODUCT of Quantity and Price for each product. As you can see the result is 0 for each row. This is just an example of my real problem with a report of 200K...
  7. Y

    Replacing Data Table with a dynamic array

    Up until the arrival of dynamic arrays to Excel I ran Monte Carlo simulations using the DATA TABLE as in range D2:D11 in the example below. Notice that each cell in that range is an independent trial of the computation in D2. Now, if I want to change the number of trials, I have to retype the...
  8. Y

    using SEQUENCE to spill the content of a single cell over a range

    Setting Suppose I want to have the numbers from 1 to 10 generated in the range B1:B10. I can store N in A1 and type =SEQUENCE(A1) in B1. A dynamic array is generated that gets the job done. My problem I have an arbitrary computation in A2 that I want to copy 10 times in the range C1:C10. I...
  9. S

    Sumproduct with a dynamic moving range

    Hi Guys, I have been trying to get results from SUMPRODUCT based on the data for volumes and %s which need to change every year but so far have no success. I have attached a mini-sheet below which shows what the result should be like. Any help is much appreciated. Thanks in Advance.
  10. DRSteele

    Split text cell into columns of words.

    If you have a cell containing a text string with spaces in it and you want to split it by spaces into columns, you can easily do so by using the Text-to-Columns functionality. Or you can use Power Query to clean up and split everything. Or if you want to use formulas, this algorithm can help...
  11. MrExcel

    Using a Date in the SEQUENCE formula

    As of November 26, 2019, the Excel team has announced that Dynamic Arrays have rolled out to 20% of Office 365 Subscribers on the Monthly channel. This means tens of millions of people are getting their first look at SORT, SORTBY, FILTER, UNIQUE, RANDARRAY, and SEQUENCE. In my seminars, I...
  12. S

    Creating a Dynamic Floor plan

    This is not looking for a solution its more so advice on if its possible. Was looking to create a dynamic floor plan in Excel where on the raw tab you would enter certain dimensions of a piece of equipment and it would then map it on a sheet. Something like Item A being 6*3 meaning its 6...
  13. L

    Create Arrays from external workbook and add worksheet table data to the arrays

    I have a workbook that will be my main program. From this workbook I need to do the following in VBA. 1) Reference external workbook (hardcoded location). 2) Create arrays based on the sheet names in this external workbook 3) Populate each array with the table data in each worksheet once the...
  14. D

    Automated Dashboard

    Hi Guys, I am trying to make myself a dashboard that is updating itself as much as possible. I have one data sheet with all the source data which is in the following format: <tbody> Startdatum new members New member % page views visits 4.1.16 wk 1 0 0 0 0 11.1.16 wk 2 0 0 0 0...
  15. C

    SUMIF with Dynamic Array/range

    HI, I have a fixed asset register set up as follows: Cell A1 date Col A - Location Col N to Y - Jan to Dec. Each column contains the monthly depreciation value for each asset. I need to do a sum based on the location, but I need the sum range to change to give me the YTD values. so for...
  16. D

    Repeat a dynamically sized array in one column

    Hi. I'm not sure this is possible but here goes. And thanks in advance for any insight, even if that insight is "you can't". I have a number of dynamically sized, named arrays that I want to repeat in a column. For instance, the baseline array might have four values in a column (representing 4...
  17. E

    Creating charts with multiple data series (in a loop)

    Dear All, I am struggling trying to set up a code in VBA to create a chart in the active w/s. This chart should be a scatter line with no markers. The x values are constant for all the series (column A from A19 - the end will be dynamic). The y values will vary. The number of series will also...
  18. S

    How would I add Multiple rows in active worksheet (Values to be taken from TextBox)?

    How would I add Multiple rows in active worksheet (Values to be taken from TextBox)?
  19. N

    Dynamic VLOOKUP within a expanding/contracting column range

    VERSION Excel 2013 (15.0.4631.1000) SUMMARY OF ISSUE Creating a dynamic VLOOKUP within a expanding/contracting column range BACKSTORY Within my business, I have an internal reporting suite of which I export monthly sales data. The data details which shops have generated sales. Some months all...
  20. S

    Referencing a specific Column in a named range

    Probably a newbie question, but I have the formula below that finds rows wherever column Q is blank and sends back the row number {=IF(ISERROR(SMALL(IF('Production Meet. Mins '!$Q$13:$Q$99="",ROW('Production Meet. Mins '!$Q$13:$Q$99)),ROW(1:1))),"",SMALL(IF('Production Meet. Mins...
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top