dynamic array

  1. 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...
  2. 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...
  3. 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...
  4. 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...
  5. 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?
  6. 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...
  7. 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...
  8. 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...
  9. 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 -->...
  10. B

    Using CountIf Using Variable Rows

    I am trying to use CountIf, but making the Range using a formula to find a row in another sheet. It is a little bit complex and I haven't found any examples or advice on how to do this correctly on any tutorials. I am hoping someone here could help me figure this out. Here are the two sheets...
  11. T

    Median & Percentile for the range of various lengths

    Hi, I am trying to calculate the Median & 75th Percentile for the range in array where the range differs in the length. For example, my data looks like the one given below (my data-set is almost 100,000 rows). Now I am trying to find the median and 75th percentile values of a combined set of 3...
  12. Z

    SUM IF with Dynamic ranges

    Hello All, I am really new to this one.. and kinda got stuck. I am trying to add dynamic range and sum range to the SUMif Function.. but it's not working for me. when i break down the sum and the dynamic ranges its seems like they are working but not when i add them to the sumif. any ideas?
  13. C

    Large function

    I have sales data for several weeks for over 100 products one sheet. Week numbers horizontally across the top, Product details vertically down the left. I have created a drop down list (separate sheet) of week numbers and I want to return the top 10 largest sales values depending on the week...
  14. W

    Redim doesn't work after script was run successfully

    Hi there, I managed to run the script only once without error. But now I get a value error and it seems that the Redim is not resizing the arrays. Originally I used Redim Preserve but after the error I also tried to resize them at the initialization, but neither of them seems to work now. Here...
  15. J

    Dynamic Regressions.

    SO the issue I am having is that I need to create a macro that copies information into a new sheet and then runs a regression on that information. Sounds simple enough, right? So I recorded this Macro: Sub h() ' ' h Macro ' h ' ' Keyboard Shortcut: Ctrl+h '...
  16. E

    Can Excel dynamically plug into a formula the last row (rows are added every week)

    I have a spreadsheet where one or two new rows are added every week. When I summarize my report I pick from row 2 to row n (last row value). The report has about 30 cells and currently I have to retype the last row number 30 times) I wanted to see if I can code something so that my report is...
  17. R

    Dynamic Rank Formula: No Duplicates or Errors

    I've seen a number of threads that help with creating rank formulas that solve the duplicates problem by using COUNTIF statements, and others that allow for removal of blanks or #DIV/0! errors, but I'm using a set of data where cells are updating dynamically, and some of them might be numbers at...
  18. K

    Return all values

    Hello everyone- I'm not quite sure how to phrase my question, so I'll ask by way of an example. Let's say I have a dynamic data set like the one below. (Assume people are always adding, deleting, and changing entries.) <colgroup><col width="79" span="2" style="width:60pt">...
  19. C

    Dynamic Array

    Hello, I'm interested in creating a dynamic array. Essentially, I have a list of locations, and I would like that list of locations to populate into a subsequent list whenever someone places an "x" next to it. Like in the table below. Can someone please help? <tbody> Locations Place an "X"...
  20. J

    Auto-populate input box with array value

    Good morning! Using VBA I am creating a dynamic array using the values present in a text file. Users are then prompted to type in a value via an input box; if the value entered in the input box is not in the array, an error message appears, prompting them to cancel or retry their entry. In...

Watch MrExcel Video

This Week's Hot Topics

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