writing a recursive function

**rmoney** · 07-19-2010, 11:14 AM

So I have this function...

=IF('Equipment List'!N2=TRUE,'Equipment List'!A2,"-")

and now instead of displaying "-" when it fails, I want it to check the next item in the list, 'Equipment List'!N3 and display it, A3.

Now what I can't figure out is that if N3 also fails, how can I get it to keep moving down the list till it finds an item to display or reaches the end?

**Paul** · 07-19-2010, 11:23 AM

Hi Rmoney,

To scan through N2:Nx, try:

=IFERROR(INDEX('Equipment List'!A2:A10,MATCH(TRUE,'Equipment List'!N2:N10,0)),"-")

This is a 2007-2010 function that will not work in 2003 or previous versions. For compatibility with older versions, use

=IF(ISNA(INDEX('Equipment List'!A2:A10,MATCH(TRUE,'Equipment List'!N2:N10,0))),"-",INDEX('Equipment List'!A2:A10,MATCH(TRUE,'Equipment List'!N2:N10,0)))

**rmoney** · 07-19-2010, 11:42 AM

Thank you that first bit is helpful. The only problem I'm having with that now, is that I have that code copied down an entire column because I am looking to display roughly 100 items at any one time. So now some items will show up more than once.

For example...

the first row picks the third item on the list because that is the first one to satisfies the argument. Now the second row will also wind up picking the third item because it is still the first item on the list to satisfy the logic argument. So in addition to what you helped me with, I would also need it to some how check against what is already displayed so there are no duplicates.

**Paul** · 07-19-2010, 11:45 AM

I'd suggest posting a sample workbook showing the "before" and "after" with the "after" results entered manually. I'd also suggest in the future you make all requirements known up front, as technically your initial question has been resolved.

Thanks.

**rmoney** · 07-19-2010, 01:38 PM

Ok, here is the file I'm working on but I removed any irrelevent parts and non interactive sheets hidden (data tables, etc)

EXAMPLE.xlsx the serialnum is the column that tests if something should be displayed, the rest fill in info based off of the serila num.

So on the before sheet, when clicking the EXAMPLE check box it filters out any items that do not support EXAMPLE. It leaves the list with spaces for items not supported. Because of this, I can only display however many lines I dedicate. I want to be able to check a box and have the results start showing up at the top instead of leaving blank spaces for the previous entries that do not meet the requirements. The functionality of the filtering works fine, its just I don't know how to display it the way I want to.

When looking at the after sheet, and checking the EXAMPLE box, you can see that results do get pushed up but there winds up being duplicates.