I am trying to use a form of regression in my Excel Formula, and the resulting formula is too long.

A bit of background on what I am trying to do. I have 8 "task" spreadsheets in my workbook. Each "task" sheet has a listing of things that a group needs to complete. Each "task" sheet has a date, action item, status and complete (Y/N) columns. What I would like to do is create a summary sheet that includes ALL line items that are listed with the complete column = "N". What this will do is provide a global view of everything that everyone has to do. The formula is pasted at the end of this post.

If you look closely at the formula, it is a repetition of the following formula:

=IF(ISERROR(INDEX('Route Determination'!$A$1:$H$999,SMALL(IF('Route Determination'!$D$3:$D$999="N",ROW('Route Determination'!$D$3:$D$999)),ROW('Route Determination'!1:1)),1)),"",INDEX('Route Determination'!$A$1:$H$999,SMALL(IF('Route Determination'!$D$3:$D$999="N",ROW('Route Determination'!$D$3:$D$999)),ROW('Route Determination'!1:1)),2))

There are 8 instances of this formula, one for each group. Each instance is repeated in the "TRUE" case of IF(ISERROR()) (highlighted in red with ""). Basically, if there are no more tasks in one group, it moves to the next group. I call this regression because it is basically calling the same formula. Is there someway to 'alias' the formula, so that I don't have to type it for each instance.

I know this is very complex, but I would appreciate it if someone would take the time to help me figure this out. Perhaps there is a much easier way of doing this that I wasn't aware of. If you have any questions, feel free to post a reply here or even email me.

Thank you,

Dave

=IF(ISERROR(INDEX('Accounts Receivable'!$A$1:$H$999,SMALL(IF('Accounts Receivable'!$D$3:$D$999="N",ROW('Accounts Receivable'!$D$3:$D$999)),ROW('Accounts Receivable'!1:1)),1)), IF(ISERROR(INDEX('Accounts Payable'!$A$1:$H$999,SMALL(IF('Accounts Payable'!$D$3:$D$999="N",ROW('Accounts Payable'!$D$3:$D$999)),ROW('Accounts Payable'!1:1)),1)), IF(ISERROR(INDEX('Internal Orders'!$A$1:$H$999,SMALL(IF('Internal Orders'!$D$3:$D$999="N",ROW('Internal Orders'!$D$3:$D$999)),ROW('Internal Orders'!1:1)),1)), IF(ISERROR(INDEX('Work Centers'!$A$1:$H$999,SMALL(IF('Work Centers'!$D$3:$D$999="N",ROW('Work Centers'!$D$3:$D$999)),ROW('Work Centers'!1:1)),1)), IF(ISERROR(INDEX('Customer Calendar'!$A$1:$H$999,SMALL(IF('Customer Calendar'!$D$3:$D$999="N",ROW('Customer Calendar'!$D$3:$D$999)),ROW('Customer Calendar'!1:1)),1)), IF(ISERROR(INDEX('Pack Determination'!$A$1:$H$999,SMALL(IF('Pack Determination'!$D$3:$D$999="N",ROW('Pack Determination'!$D$3:$D$999)),ROW('Pack Determination'!1:1)),1)), IF(ISERROR(INDEX('Route Determination'!$A$1:$H$999,SMALL(IF('Route Determination'!$D$3:$D$999="N",ROW('Route Determination'!$D$3:$D$999)),ROW('Route Determination'!1:1)),1)),"",INDEX('Route Determination'!$A$1:$H$999,SMALL(IF('Route Determination'!$D$3:$D$999="N",ROW('Route Determination'!$D$3:$D$999)),ROW('Route Determination'!1:1)),2)),INDEX('Pack Determination'!$A$1:$H$999,SMALL(IF('Pack Determination'!$D$3:$D$999="N",ROW('Pack Determination'!$D$3:$D$999)),ROW('Pack Determination'!1:1)),2)),INDEX('Customer Calendar'!$A$1:$H$999,SMALL(IF('Customer Calendar'!$D$3:$D$999="N",ROW('Customer Calendar'!$D$3:$D$999)),ROW('Customer Calendar'!1:1)),2)),INDEX('Work Centers'!$A$1:$H$999,SMALL(IF('Work Centers'!$D$3:$D$999="N",ROW('Work Centers'!$D$3:$D$999)),ROW('Work Centers'!1:1)),2)),INDEX('Internal Orders'!$A$1:$H$999,SMALL(IF('Internal Orders'!$D$3:$D$999="N",ROW('Internal Orders'!$D$3:$D$999)),ROW('Internal Orders'!1:1)),2)),INDEX('Accounts Payable'!$A$1:$H$999,SMALL(IF('Accounts Payable'!$D$3:$D$999="N",ROW('Accounts Payable'!$D$3:$D$999)),ROW('Accounts Payable'!1:1)),2)),INDEX('Accounts Receivable'!$A$1:$H$999,SMALL(IF('Accounts Receivable'!$D$3:$D$999="N",ROW('Accounts Receivable'!$D$3:$D$999)),ROW('Accounts Receivable'!1:1)),2))