Need help modifying this array formula which returns all records meeting a certain criteria on another tab

[Shellecj]

Hello,

I'm setting up an array formula on another worksheet within my workbook to return all records that meet a certain criteria (have an * in a certain column named "Qual"). I've used this same formula before in several other workbooks (just modify the ranges, etc as needed) but in this particular workbook I'm working on, the range of data starts at row 10 (row 10 being the header rows) so the formula below does not work because the array wants to start at row 1. I have other data (titles, logo, etc) above this range so don't really want to delete the first 9 rows. Is there a way to make this formula work by offsetting by 10 to start at row 10? Or does anyone have any other suggestions for any other formulas which would do this?

The formula I have set up so far is below, and resides on another sheet, starting a few rows down to save room for title/logo, etc on the page (this page will be printed) and is an array formula copied down about 20 rows.

{=IFERROR(INDEX('Task Flow'!$A$10:$AZ$195,SMALL(IF(Qual="*",ROW(Qual)),ROW(1:1)),1),"")}

 

[Aladin Akyurek]

On the main sheet there's a if formula in last column....basically if each record in the column qualifies by having some task due based on a set of conditions, the formula leaves an "*" asterisk if the formula was true. Where "Qual" is in the formula above actually refers to the range 'Task Flow'!$AR$10:$AR$195 (I named the range Qual). I tried entering the formula on another sheet ("AM1") starting in cell B5 (confirmed with CTRL+Shift+Enter) and the formula is copied down about 20 rows, to leave extra room should the list of tasks that are due grows.

1) I'd anchor the formula to B5 on AM1.
2) Name 'Task Flow'!$A$10:$AZ$195 as Data.

3) B5, Control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Data,SMALL(IF(Qual="*",
    ROW(Qual)-ROW(INDEX(Qual,1,1))+1),ROWS($B$5:B5)),1),"")

 

[Shellecj]

1) I'd anchor the formula to B5 on AM1.
2) Name 'Task Flow'!$A$10:$AZ$195 as Data.

3) B5, Control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Data,SMALL(IF(Qual="*",
    ROW(Qual)-ROW(INDEX(Qual,1,1))+1),ROWS($B$5:B5)),1),"")

Hi Aladin...sorry for the slow response. I just getting home from work and seeing your post now. I could not get the formula to work right off the bat but in playing around with it, I changed the +1 to a +2 and finally got the right results. -> =IFERROR(INDEX(Data,SMALL(IF(Qual="*",
ROW(Qual)-ROW(INDEX(Qual,1,1))+2),ROWS($B$5:B5)),1),"")

I'll hold on to this and keep this formula in my arsenal as well.

Don't suppose you want to break it down to tell me how exactly the formula works or what it's doing?

 

[Aladin Akyurek]

Hi Aladin...sorry for the slow response. I just getting home from work and seeing your post now. I could not get the formula to work right off the bat but in playing around with it, I changed the +1 to a +2 and finally got the right results. -> =IFERROR(INDEX(Data,SMALL(IF(Qual="*",
ROW(Qual)-ROW(INDEX(Qual,1,1))+2),ROWS($B$5:B5)),1),"")

I'll hold on to this and keep this formula in my arsenal as well.

...

The +2 bit (instead of 1) would be necessary only if A10:AZ10 in Data which refers to

'Task Flow'!$A$10:$AZ$195

consists of headers. Could you confirm this?

 

[Shellecj]

The +2 bit (instead of 1) would be necessary only if A10:AZ10 in Data which refers to

'Task Flow'!$A$10:$AZ$195

consists of headers. Could you confirm this?

Yep, you are correct! I thought it was supposed to? My initial formula that I was using was based on what I had set up in another workbook, When I had set up that formula up some time ago, I could not get it to work until I set it up where my range included the header row. In the case of that workbook, the headers started in row 1 and the data below started in row 2. I also couldn't get it to work without a helper column to show an asterisk if the condition were true (I just couldn't get it to work when I had nested an if statement right there where it was referring to the *) so had to resort to using a helper column to refer to.

 

[Aladin Akyurek]

Yep, you are correct! I thought it was supposed to? My initial formula that I was using was based on what I had set up in another workbook, When I had set up that formula up some time ago, I could not get it to work until I set it up where my range included the header row. In the case of that workbook, the headers started in row 1 and the data below started in row 2. I also couldn't get it to work without a helper column to show an asterisk if the condition were true (I just couldn't get it to work when I had nested an if statement right there where it was referring to the *) so had to resort to using a helper column to refer to.

If the definitions of Data and Qual include the header row, +2 is okay. Otherwise we need +1.

 

[Shellecj]

Yes, both did. Thank you so much for the assistance! Much Appreciated!

 

[Aladin Akyurek]

Yes, both did. Thank you so much for the assistance! Much Appreciated!

You are welcome.