+ Reply to Thread
Results 1 to 5 of 5

Dynamic table arrays

  1. #1
    Registered User
    Join Date
    12-23-2015
    Location
    Ohio
    MS-Off Ver
    Unknown
    Posts
    2

    Dynamic table arrays

    I'm working on a sheet that pulls just the Inventory items which are in stock and trying to keep my code as simple a possible.

    I have several categories with in each sheet of the Inventory (INV) listed descending order. 2X, XL, LG, MD, SM, TM

    I want to pull a row reference from a column but anyone who uses the INV can add rows for new INV item, so I can not define a specific start and stop position in the column.


    This method may be futile but I have started tinkering with:

    To determine if an INV cell has a value, In $AR each category header starts with 0 and every descending cell is populated with =IF(A2>0,AR2+1,AR2)

    This allows me to search a column for each item with a positive quantity in the INV. =MATCH(1,Tabulator!AR:AR,0)

    The problem I am having is that, although designating each category header with 0 does reset the list value and allows it to start over regardless of how many rows are added. I do not know how to tell the formula how to read a variable array range.

    I need a formula that results in =MATCH(1,Tabulator!AR:AR,0)
    When = a specified Cell Value like A1 & A2 which determines the start and finish of each size category.
    or a formula that allows me to search for the second/third occurrence of 1 in AR

    I tried concatenate, and I have read many threads on Dynamic Vlookup but they are not helping. I have 31 sheets of ever changing INV each month. I can reference the data, I just need to pull the correct row value within each category to form a simplified list of what is on hand.

    Any help would be appreciated.

  2. #2
    Forum Expert BadlySpelledBuoy's Avatar
    Join Date
    06-14-2013
    Location
    East Sussex, UK
    MS-Off Ver
    365
    Posts
    7,468

    Re: Dynamic table arrays

    I feel you would get help on this far quicker if you posted an example workbook.
    Include details of the desired results.

    BSB

  3. #3
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,570

    Re: Dynamic table arrays

    (How to) Upload a Workbook directly to the Forum
    (please do not post pictures or links to worksheets)
    • Click Advanced (next to quick post),
    • Scroll down until you see "Manage Attachments",
    • Click that then select "add files" (top right corner).
    • Click "Select Files" find your file, click "open" click "upload"
    • Once the upload is completed the file name will appear below the input boxes in this window.
    • Click "Done" at bottom right to close the Attachment Manager.
    • Click "Submit Reply"
    Note: Please do not attach password protected workbooks/worksheets
    Ben Van Johnson

  4. #4
    Registered User
    Join Date
    12-23-2015
    Location
    Ohio
    MS-Off Ver
    Unknown
    Posts
    2

    Re: Dynamic table arrays

    As stated

    I need a formula that results in =MATCH(1,Tabulator!AR:AR,0)
    When = a specified "Value" in a cell like A1 & = A2, which determines the start and finish of a Dynamic array row range.

    Or a formula that allows me to search for the second/third occurrence of (1) in a column.

    If this is not possible that it fine. It just means that the Excel program language is too simple for basic programing. With the raw amount of effort it takes to apply a simple loop in Excel it would not surprise me if that is the case.

    I can not publish our INV and what I am requesting should be fairly clear. I can not be the first person to ask how to define an array range with a variable value. There are an infinite number of applications where such a basic formula would be needed. There has to be a simple way to accomplish this in Excel. I've tried offsets, indexing, concatenate, indirect and many more. I need to match (1) in AR on another sheet that start from the row indicated by the value in A1, not A1 itself.

    This is the best I have been able to come up with and it fails even though the formals look like they should work.
    =MATCH(1,INDIRECT("'"&"Tabulator!"&CONCATENATE("AR",A1,":AR",A2)),0)

    Again and please, any help is appreciated.
    Last edited by MrGawain; 12-23-2015 at 10:33 PM.

  5. #5
    Valued Forum Contributor
    Join Date
    12-28-2014
    Location
    NSW, Australia
    MS-Off Ver
    MS365
    Posts
    604

    Re: Dynamic table arrays

    Try:
    Please Login or Register  to view this content.
    maybe...hard to test without a sample sheet.


    Also, if not.....

    Please Login or Register  to view this content.
    should at least tell you where it is trying to look. Once you get that looking right, insert it into your INDIRECT.
    Last edited by Beamernsw; 12-24-2015 at 02:00 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Dynamic arrays
    By Rockafella in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-22-2012, 08:30 AM
  2. Graphing Dynamic Arrays
    By ih8xc in forum Excel Charting & Pivots
    Replies: 6
    Last Post: 03-12-2011, 07:37 AM
  3. Using Dynamic Arrays
    By ozi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-27-2007, 09:35 AM
  4. RE: Dynamic multidimensional arrays
    By Tom Ogilvy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-07-2006, 02:55 PM
  5. RE: Dynamic multidimensional arrays
    By Jim Thomlinson in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-07-2006, 02:45 PM
  6. Dynamic Arrays
    By chaz in forum Excel General
    Replies: 1
    Last Post: 05-22-2006, 07:50 PM
  7. Dynamic arrays
    By Driver in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 11-07-2005, 06:15 PM
  8. Dynamic Arrays
    By Chiba in forum Excel Formulas & Functions
    Replies: 24
    Last Post: 09-06-2005, 12:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1