dynamic arrays

  1. 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...
  2. 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...
  3. 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...
  4. 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...
  5. 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...
  6. 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...
  7. 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...
  8. 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...
  9. 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)?
  10. 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...
  11. 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...
  12. X

    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...
  13. C

    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...
  14. R

    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...
  15. G

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

    Populating a User Form Text Box with a list of file names

    Hello All, What is the best way to populate a text box with a comma separated list of file names at a directory? Can someone show me what an array might look like for an in determinant number of file names (1 to x) perhaps?) Shaun
  17. ed.ayers315

    dynamic sumifs

    Hello All, I am trying to figure out how to get this spreadsheet to wrok the way I need it to. I insert the "Green" portion below from "NotePad". It is always in this order and format. I need to track and total when personnel use too many of the same "Recipe" in a given time period. I have...
  18. R

    Filling dynamic arrays vba

    I am trying to fill a dynamic array with the row indexes from a search function. So far I have created a loop that introduces a leading or trailing 0 to fill the array. I would like to fill the array without the 0 but my current code keeps giving me a 'subscript out of range' error. This...
  19. A

    How To Group like sheets by name then print & loop

    Hi, Below is my code that will loop through a workbook and print each worksheet individually. I'm now trying to add code in order to find worksheets with the same account number in the sheet name and group them - then print, and move on the the next account... below is an example of 2...
  20. S

    Filling array into Combo box

    Hi I am new to this forum, and would like to get help on the usage of array inside a combo. I am tring to populate a combo box (single coulmn) with an array (dynamically created) . But Iam not sure how to do it. Would like to get get help from this forum I am trying to get something like...

Some videos you may like

This Week's Hot Topics

Top