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.
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.
Try this (simpler) array formula:
=OFFSET(INDEX($P$2:$OD$2,,MATCH(TRUE,P3:OD3<0,0)),,-1,1,1)
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
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 ,
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.
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.
Thank you for the big help, and also many thanks for the additional help with no error.
You're welcome and thanks for the rep.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks