+ Reply to Thread
Results 1 to 4 of 4

Spill Range Across Rows and Columns

  1. #1
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Spill Range Across Rows and Columns

    Hello Excel Gurus,

    Given the Dynamic Array Functions, I am wondering if there's a way to do a spill result on rows and columns, so that the formula updates dynamically, instead of changing it manually?

    What I meant by that is, in the past, below are the steps that I'd usually do:
    1. Create a MATCH Function at cell B2
    2. Copy the formula on cell B2 and paste it from B2:M9
    3. Copy and Paste/Delete formula depending on the size (going down the row and/or going across the column)


    Thus my question now is, how do I put a '1' when it's found dynamically?

    SpillRangeRowsAndColumns.png

    Note that picture shows as a spill range for cell A2# and B1#.

    I have attached a workbook, but it's not the formulated solution...instead, it's a desired solution that I've manually input.
    Attached Files Attached Files
    Last edited by dluhut; 10-28-2021 at 07:22 PM.

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

    Re: Spill Range Across Rows and Columns

    How about
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: Spill Range Across Rows and Columns

    I didn't know that the formula could be so simple...and yet here been trying to use nested SEQUENCE, MMULT, INDEX etc.

    Thanks Fluff13. Reps up to you and Solved.

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

    Re: Spill Range Across Rows and Columns

    You're welcome & thanks for the feedback.

+ 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] Data sorting multiple rows across columns (2D arrays) with only ONE "spill" formula
    By leolapa in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-24-2021, 01:40 PM
  2. [SOLVED] Removing #spill! and then #N/A from the range
    By homa5424 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-01-2021, 07:01 AM
  3. [SOLVED] Dynamic Array Spill and Sort into 2 Columns
    By philchi in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-03-2021, 04:54 PM
  4. Replies: 5
    Last Post: 01-27-2021, 12:07 PM
  5. [SOLVED] String-to-columns spill formula
    By ExcelFan117 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-10-2020, 07:09 AM
  6. Spill from list is returning in columns instead of rows
    By xTyD23x in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-18-2020, 03:26 PM
  7. UDF For spill range(implicit intersection)
    By daboho in forum Excel Programming / VBA / Macros
    Replies: 18
    Last Post: 06-28-2019, 08:22 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