FORMULA TROUBLESHOOTING

[markstro]

The formula below is meant to return the column heading if any cell in the column below that heading is greater than Zero.
It works quite well up to row 8098, row 8099 on down it goes wrong and I cannot figure out why.
It should return col 27 (AA) for row 8099 but it returns col 22 (V).
Can anyone help me with why the formula is failing after row 8098?
All cells are formatted as "General" by the way, don't think that is the problem, but an answer to an anticipated question.

=IFERROR(INDEX($1:$1,SMALL(IF($V8098:$BC8098>0,COLUMN($V$1:$BC$1)),COLUMNS( $B$1:B8092))),"")
=IFERROR(INDEX($1:$1,SMALL(IF($V8099:$BC8099>0,COLUMN($V$1:$BC$1)),COLUMNS( $B$1:B8093))),"")

[Leith Ross]

Hello markstro,

Excel has an upper limit of the number of contiguous cells that can be referenced in certain ifunctions. Microsoft set the number at 8192 but in reality it is sometimes a little more or a little less.

[Aflatoon]

I think it would help if you posted a sample workbook. I don't really see the point of your last argument - as you copy down it will return the same number so you don't need the row to increment.

[markstro]

Thanks for the replys, I found out if I select the blank cells and hit delete the formula corrects itself. My only headache now is how to do the same for all blank cells in the range V8098:BC26448.
In the future I will have to perform the same "delete blank cells" solution to the data I add each month.

Aflatoon, I will also try changing the last argument, back to you soon.

[Aflatoon]

Select the data, then run this:

Sub CleanSpace()
    With Intersect(ActiveSheet.UsedRange, Selection)
      .Value = ActiveSheet.Evaluate("IF(ROW(" & .Columns(1).Address & "),trim(" & .Address & "))")
    End With
End Sub

[markstro]

Aflatoon, Perfect, I love it, cleared all my formula return errors.

[Aussiebear]

Wouldn't it be better to understand why your formulas return "errors" and then reconstruct the formulas?

[markstro]

It's not the formula, it's the cells in the range feeding the formula.
How does a cell appear blank but prevents an array formula from working properly.

All I know is when I select all blank cells, press "Delete" something goes away and the formula works again.

I am copying monthly data from my vendor and pasting into my spreadsheet, I'm evidently copying data with a hidden value or other data in those cells.

Aflatoon's VBA trims the cells as far as I can tell, works for me.

[Aussiebear]

Fair enough, but this raises another question. If Aflatoon's code "trims the cells", how can it trim a blank cell?

[markstro]

Not Sure, perhaps you can tell me what "trim" does in the VBA below.
Sub CleanSpace()
With Intersect(ActiveSheet.UsedRange, Selection)
.Value = ActiveSheet.Evaluate("IF(ROW(" & .Columns(1).Address & "),trim(" & .Address & "))")
End With
End Sub

Something is removed from those seemingly blank cells in V:BC in that range in my formula below.
=IFERROR(INDEX($1:$1,SMALL(IF($V8099:$BC8099>0,COLUMN($V$1:$BC$1)),COLUMNS( $B$1:B8093))),"")

What is the difference between deleting a blank cell and applying the VBA below.
Both commands do the same thing as far as the returns for my array formulas.

[Aflatoon]

I use this code a lot. If you copy and paste special-Values cells that contained a formula returning "", the result is a cell that appears blank but isn't - it actually contains a zero length string. The code clears those to truly blank cells.

[markstro]

Much appreciated, this problem has plagued me for a long time.
I spent a lot of time selecting all those blank cells and pressing delete to clear them, not any more.