Using Regression in formulas (Formula too long)

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))

Dave

My thought for this type of thing is to build your own function.

Assumptions:
1) Your summary sheet is the first sheet in the file, and all the subsequent sheets are your data sheets.
2) Data is in columns A-D, with the first row as headers.

Put the code below into a general module in the workbook.

Please Login or Register  to view this content.

Then in your summary sheet (ie the first sheet).
A2: =myfunc(ROW()-1,COLUMN())

Copy down / across for columns A:C inclusive. Column D is not filled as it will only contain N.

See if that gets you going.

rylo

From what my research shows, I need at least Office97 to do this, is that correct. I'm currently running Office Professional 2003 SP2, and I can't for the life of me figure out how to do this. Do I need to upgrade? I probably should, it's about time.

Let me know what you guys think,

Dave

May I see a sample workbook.

Yes, but I'm going to need a few minutes to clean up my work, and more importantly, make sure it is free of all proprietary information before I post it.

Here is a sample workbook. The first sheet is the summary that I would like to display all tasks with a completion status = "N". Right now, that is set-up only to work with the Route Determination sheet.

Let me know if you have any questions.

Dave

Hope this helps!

Thank you, this is great. Would you mind explaining in a few brief lines how this is works? I had to make sure Macros were enabled in order to open this, did you use macros? How?

Thanks to everyone for all of your help, I greatly appreciate this.