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

1. ## 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.

2. ## 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)

3. ## 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. ## 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.

5. ## 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:

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.

6. ## 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. ## Re: Expand range of offset/indirect formula to expand to entire range

You're welcome and thanks for the rep.

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

#### 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