+ Reply to Thread
Results 1 to 7 of 7

Expand range of offset/indirect formula to expand to entire range

  1. #1
    Registered User
    Join Date
    10-04-2018
    Location
    Buzau
    MS-Off Ver
    2010
    Posts
    17

    Expand range of offset/indirect formula to expand to entire range

    Dears,

    Please help expand the formula coverage on my table as it does not work properly when I'm having a negative value for December it gives me Ref, I have attached the table so you can see exactly the rows and columns that I have.
    Attached Files Attached Files

  2. #2
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: Expand range of offset/indirect formula to expand to entire range

    Try this (simpler) array formula:

    =OFFSET(INDEX($P$2:$OD$2,,MATCH(TRUE,P3:OD3<0,0)),,-1,1,1)
    Attached Files Attached Files
    Glenn



  3. #3
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: Expand range of offset/indirect formula to expand to entire range

    or a similar non-array version:

    =OFFSET(INDEX($P$2:$OD$2,,MATCH(TRUE,INDEX((P3:OD3<0),0),0)),,-1,1,1)

    Given your loaction, you may need ; as the spearator instead of ,

  4. #4
    Registered User
    Join Date
    10-04-2018
    Location
    Buzau
    MS-Off Ver
    2010
    Posts
    17

    Re: Expand range of offset/indirect formula to expand to entire range

    Thanks for the help, but unfortunately it still does not work or I do not know how to put it correct... please see the table as it is completely to be sure nothing is missed.
    Attached Files Attached Files

  5. #5
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: Expand range of offset/indirect formula to expand to entire range

    One of the main reasons that it did not work seems to be that you did not use EITHER of the solutions that I suggested...
    This is the array formula that you used:

    =OFFSET(INDIRECT(CELL("address",INDEX(P5:BE5,1,MATCH(TRUE,P5:OD5<0,0)))),ROWS($5:5)*-1,-1,1,1)

    Apart from being totally different (!!) you did not $ the INDEX range.

    Use this:

    =OFFSET(INDEX($P$4:$OD$4,,MATCH(TRUE,INDEX((P5:OD5<0),0),0)),,-1,1,1)


    If you want to get rid of the #N/A errors which appear when there are no negative values in the range, then use this:

    =IFERROR(OFFSET(INDEX($P$4:$OD$4,,MATCH(TRUE,INDEX((P5:OD5<0),0),0)),,-1,1,1),"No Negatives")

    Change the "No Negatives" to whatever error message you want to see, or use "" to return a blank.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    10-04-2018
    Location
    Buzau
    MS-Off Ver
    2010
    Posts
    17

    Re: Expand range of offset/indirect formula to expand to entire range

    Thank you for the big help, and also many thanks for the additional help with no error.

  7. #7
    Forum Guru Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Northern Ireland
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    24,317

    Re: Expand range of offset/indirect formula to expand to entire range

    You're welcome and thanks for the rep.

+ 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] SUMIF formula with dynamic column range and then expand the width of SUM range
    By gravythief in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-26-2017, 08:36 AM
  2. [SOLVED] Expand a range when you click any cell in the last line of the range
    By Dickie Dunkin in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-17-2016, 03:32 PM
  3. [SOLVED] Expand With Range Formula
    By timbo1957 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-26-2013, 07:36 AM
  4. Replies: 2
    Last Post: 06-14-2012, 01:44 PM
  5. Expand a Range non VB
    By starbwoy in forum Excel General
    Replies: 3
    Last Post: 03-27-2008, 12:17 PM
  6. Expand range help
    By David in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-26-2006, 08:35 PM
  7. Replies: 1
    Last Post: 07-07-2005, 09:05 AM

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