+ Reply to Thread
Results 1 to 4 of 4

Building Arrays without a sequence

  1. #1
    Registered User
    Join Date
    05-16-2022
    Location
    UK
    MS-Off Ver
    Excel 365
    Posts
    1

    Building Arrays without a sequence

    Hello

    I have the following formula.
    =INDEX(FILTER($A$3:$O$63,ISNUMBER($M$3:$M$63)),SEQUENCE(ROWS($A$3:$O$63)),{2,3,13})

    I would like to make it dynamic so I can drag across my various columns e.g.:

    =INDEX(FILTER($A$3:$O$63,ISNUMBER($M$3:$M$63)),SEQUENCE(ROWS($A$3:$O$63)),{2,3,14})
    =INDEX(FILTER($A$3:$O$63,ISNUMBER($M$3:$M$63)),SEQUENCE(ROWS($A$3:$O$63)),{2,3,15})

    I thought about using INDIRECT(A1) but this does not seem to work. Where A1 = {2,3,13}

    Any thoughts would be much appreciated - thanks

  2. #2
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Building Arrays without a sequence

    Hey there,

    If you want that 13 to change automatically when you copy the formula across one row, you could try:

    Please Login or Register  to view this content.
    Tricky to be completely certain without seeing your data and there are some questions about your approach but it might work ...

    WBD
    Office 365 on Windows 11, looking for rep!

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,075

    Re: Building Arrays without a sequence

    I would like to make it dynamic so I can drag across my various columns
    If you drag that formula across then you are going to get spill errors. So I don't understand what you mean.

  4. #4
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Building Arrays without a sequence

    Yeah. That's pretty much my concern. It returns 3 columns of data. It also tries to return potentially more rows than are filtered. I think it would make more sense like this:

    Please Login or Register  to view this content.
    I guess it's returning columns B, C, and M but then the OP is asking for columns N, O, ...

    Strange,

    WBD

+ 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] 2 Arrays with same elements but sequence different
    By ImranBhatti in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-13-2018, 02:29 AM
  2. Find a short-sequence in a longer sequence (Clash Royale Chests Challenge)
    By GeneralDisarray in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-08-2016, 12:20 PM
  3. Replies: 7
    Last Post: 06-08-2015, 08:39 AM
  4. [SOLVED] building arrays from cells and/or ranges
    By Leon V (AW) in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-06-2013, 05:09 AM
  5. building a revenue forecasting model and need calendar building help
    By miken33 in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 03-11-2013, 06:53 PM
  6. Replies: 2
    Last Post: 10-08-2012, 12:49 PM
  7. Replies: 0
    Last Post: 02-22-2011, 11:31 AM

Tags for this Thread

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