dynamic arrays

1. 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...
2. 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...
3. 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...
4. 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...
5. 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.
6. 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...
7. 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...
8. 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...
9. 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...
10. 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...
11. 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...
12. 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...
13. 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...
14. 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)?
15. 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...
16. 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...
17. creating multidimensional arrays from a table and using indexes to access specific a value

-- Sample Data--- <colgroup><col width="64" span="4" style="width: 48pt; text-align: center;"> </colgroup><tbody> <colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody> Grandparent Parent Child Fee 1 ON A 5 1 ON B 10 1 ON C 15...
18. The Hardest Challenge Ever!...Need help with a formula that extract and sorts unique values from a data set.

Hi everyone, I have been searching for a formula that would do the following, extract unique values from a frequency chart..Please excuse me I am new to this form and limited knowledge of excel. (I am not a spreadsheet master..lol) example of the chart: <tbody> a b c d e 1 gas 205 2...
19. How to do Totals where the start and the end of the array can vary?

Excel 2010 <colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead> A B C D E F G H I J K L M N O P Q R </thead><tbody> 1 Project Budget 2 3 Project Name...
20. Using values from an array in Offset

I'm trying to use an array to store offset values that refer to cells whose contents I want to change only while my macro is running, and then at the end of the macro I want to revert to the value I had when the macro started. The cells can only contain "include" or "exclude", which control...

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.

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