dynamic array

  1. 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...
  2. 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...
  3. 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...
  4. 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 -->...
  5. 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...
  6. 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...
  7. 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?
  8. 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...
  9. 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...
  10. 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 '...
  11. 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...
  12. 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...
  13. 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">...
  14. 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"...
  15. 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...
  16. J

    Structured reference and offset

    I am using offset and structured refs in a table to create dynamic arrays. =OFFSET(Table2[[#Headers];[number]];1;0;ROW([@number])-ROW(Table2[[#Headers];[number]]);1) My intention with this formula is to create an array that grows in height for each row. But the return value is just a single...
  17. Dr. Demento

    Unknown number of dynamic arrays - how to?

    I've put together a sub that formats the page setup aspect of my sheets. However, there are instances where it falls apart, such as when a table (long & narrow) and a pivot table (or three) (short & wide) occupy the same sheet; in this case I wind up with ridiculously small print (all columns...
  18. E

    Help with Dynamic Array

    <tbody> Can someone please help me with this? I'm really struggling. Write a macro reports the sum of squares of negative #'s, sum of squares of the positive #'s, the maximum and the minimum numbers in a messagebox. You do not know the size of the table but can assume the user will choose the...
  19. M

    Arrays issue

    Hello All, I hope some else has seen or dealt with this issue. I just can not seem to find a solution. I have a large Excel file that is generated by another program. I am breaking the data up into separate tabs based on one column of data. To do this I need to make sure the other columns...
  20. Dr. Demento

    Error trapping for dynamic array

    I'm extraordinarily dense today because I can't figure out how to include error handling when creating a dynamic array that may be empty. The primary project is separating data onto individual sheets; I'm using hiker95's solution found here - awesome work, btw. However, since my arrays can be...

Some videos you may like

This Week's Hot Topics