dynamic array

  1. AWM21

    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...
  2. T

    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 -...
  3. T

    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...
  4. M

    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...
  5. P

    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...
  6. B

    dyamic array/range - rolling equation

    I have a few inputs in A1:B3: months 20 Price per month 30 cost 180 then in: A5 =sequence(1,b1,1,1) A6 =sequence(1,b1,B2,0) A7 =If(A5#=1,b3,0) This gives me a useful and dynamic table. However, I would add a rolling P&L from A8 for the length of the number of months? Below is the result that...
  7. M

    Using SUMIF with Dynamic Arrays to sum column totals

    I am trying to use a sumif with a sheet that has been set up with dyanmic arrays (see below image) I need to sum each column based on the flags "A,B or C". Is anyone able to suggest a formula that will spill and allow me to do this?
  8. N

    Using MAX() to output multiple values from one formula

    Hello (again) mrexcel forums, I'm working on a formula which outputs an array of values in an n*m grid, where the size of the grid may vary a lot between use cases. I've been getting good mileage with using arrays as named variables in equations, but i'm having trouble with the MIN/MAX...
  9. N

    Get index/position of value in dynamic range

    Hello mrexcel community, Unless i am completely inept at using the search function, this doesn't seem to be a redundant question, and i would be grateful for any help with solving this puzzle. The problem is performing a lookup on a function which outputs multiple values (and thereby spills...
  10. T

    filter dynamic data without filter function

    Hello, I'm needing help to find a way to filter data without using the updated filter and sortby functions released in excel 365. Is it possible to filter data using multiple criteria with basic excel functions? I've attached a sheet with cell A4 using the filter function to produce the desired...
  11. pi

    Pivot table with dynamic sheet (!) reference

    There is a well know solution to use dynamic ranges as source of a pivot table (name using offset). In my case I want to take it one step further. I want to put the name of a sheet into a cell and derive the source of the pivot table from that. Here is what I tried: 1: I have defined a name...
  12. W

    VBA Export selection of a Dynamic Array (FILTER) to new workbook

    Good afternoon, Is there anyway, using VBA, to export a particular range of data that's been populated with a dynamic filter from my current workbook to a new workbook? My current workbook has 5 sheets. I want to export a range of data from Sheet1 to its own workbook. In Sheet1, I have Columns...
  13. S

    Dynamic Index Array

    Hi, I'm not sure if this is possible so would appreciate any responses. I'd like to be able to use a named range in an index but would like the named range to be dynamic. For example ideally the formula would be like =index(cell reference to dynamic array, etc). The problem is I don't...
  14. R

    Dynamic "Columns:=Array(1,2,3)" in RemoveDuplicates excel VBA

    I am new to VBA. Currently I am using this, lastColumn is last column of worksheet. lastRow is last row of worksheet. It is working fine for: ActiveSheet.Range(Cells(1,1), Cells (lastRow,lastColumn)). RemoveDuplicates(Columns:=Array(1,2,3), Headers:=xlNo) - But it will work only for 3 columns...
  15. DRSteele

    Create a Dynamic Calendar with function SEQUENCE and some formatting

    Let’s create a dynamic calendar in Excel the quick and easy way. We can use the clever new Excel function SEQUENCE in Excel 365 Insider. Mr. Excel (Bill Jelen) sparked this idea with this video and I enhanced the concept so that all we need is a date to make the calendar matarialise in the...
  16. P

    Dynamic arrays missing in 1911 (but working in another installation)

    I have two different but identical computers with Office 365 (up to date to 1911, monthly channel) + Win 10 (both x64). In one computer Excel dynamic arrays work as expected, but not in the other. Should this happen and is there any way to "fix" it?
  17. DRSteele

    Loan Amortisation Schedule

    We can use Excel's new calc engine and dynamic array functions to create a loan amortisation schedule in a way that omits the need to create the correct number of rows or complex formulas to account for the correct number of rows. The formulas go in one row and they spill down to the correct...
  18. S

    Find a number from multiple range of numbers

    Dear All, I have two tables as mentioned below Master Date Table No.1:- <tbody> Serial No. Quantity of Cards Start No. End No. Invoice No. 1 500 193201 193700 1A 2 1000 199701 200700 1B 3 1000 214301 215300 1C 4 1000 223101 224100 1D 5 1000 235101 236100 1E 6 1000 239601 240600 1F...
  19. T

    Data will change if the total is adjusted

    Hi, I was wondering if this is even a possibility or if anyone has done something like this where they could help me. I receive spreadsheets from our clients (with two columns) the amounts they need us to withdraw from their bank account weekly. and there's a rounding issue. Example, even...
  20. C

    Vlookup, Hlookup, Match, Index,... or probably a combination?

    Hello, I'm looking for an Excel Formula and I hope that someone can help me out with this? I tried to draw a simplified version of the Excel below. I'm looking for a dynamic formula which returns the required training level for a specific Team. For example: - For Team C - For Training 6 -->...
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