Excel IF Formulas

[Davis10]

I have a formula that looks on a different tab in the workbook, and if in the row the formula looks there is a "Y" then it retrieves the persons information and populates the cells in the tab with the formula. Right now the formula looks like this:


=IF(ROW()-6>COUNTIF(Attendees!$J$6:$J$400,"Y"),"",SMALL(((Attendees!$J$6:$J$400="Y")*(ROW(Attendees!$J$6:$J$400))),(ROW())-6+COUNTIF(Attendees!$J$6:$J$400,"<>"&"Y")))

But the column in which "Y" would be has changed from J ,in the formula above, to column M. However when I change the J to an M, I get an error that says #NUM. I have tried everything I can think of, any idea why I get an error?

 

[jakuza]

Davis,

I think it is something with formatting of cells in column M. Inspect it's formatting and make sure it is the same as it was in column J. There is no way that the formula is not working if you simple change only column you reference to.
Also use Tools > Formula Auditing > Evaluate Formula to see when and where exactly it show an error.

 

[BenMiller]

Find one of the cells which contain Y and type =A1="Y" (replace A1 with the cell reference) and see if it returns TRUE. If not, check for leading or trailing spaces.

 

[Davis10]

It was an array formula so I just needed to ctrl-shift-enter instead of just enter. Thanks for all the help!