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]

What is the exact reference of Qual and in which cell did you try this formula?

 

[bschwartz]

Try this with Ctrl+Shift+Enter:

=IF(ROWS(A$1:A1)<=COUNTIFS(qual,"*"),INDEX('Task Flow'!$A$10:$AZ$195,SMALL(IF(qual="*",ROW(qual)-ROW($A$10)+1),ROWS(A$1:A1)),COLUMN(A$1)),"")

 

[Shellecj]

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.

 

[bschwartz]

What happened when you entered this?

Try this with Ctrl+Shift+Enter:

=IF(ROWS(A$1:A1)<=COUNTIFS(qual,"*"),INDEX('Task Flow'!$A$10:$AZ$195,SMALL(IF(qual="*",ROW(qual)-ROW($A$10)+1),ROWS(A$1:A1)),COLUMN(A$1)),"")

 

[Shellecj]

Hi bschwartz, sorry, I could not get the formula you suggested to work...

 

[bschwartz]

What is the result?

 

[Shellecj]

#REF! error

 

[bschwartz]

Interesting. It is working on my sample spreadsheet.

Can you tell me, what is the cell reference within Qual where the first * appears?

 

[Shellecj]

Nevermind! I'm so sorry, I got it to work!! I had deleted the client's name before "Task Flow" which was part of the sheet tab name, for confidentiality. Just put the name back in to match sheet name and it's working now! Thank you so much! You've helped me several times today actually!