Please help with a demon workbook!!!

Hello,
I have 'inherited' a workbook which has been tampered with in some way and no longer works properly.
I have to fix it, but sadly I am getting nowhere. I would be grateful for any help!

The front sheet is a work schedule. Rows from this are supposed to be copied onto two other sheets, depending on a) which name is identified and b) the urgency of the task.
The main formula used throughout is
=IF(ISERR(INDEX(Schedule!$I$23:$I$125,SMALL(IF(Schedule!$I$23:$I$125="Important",ROW(Schedule!$I$23:$I$125),306),ROW()-ROW($I$4)+1)-ROW($I$4)+1)),"",INDEX(Schedule!C$23:C$101,SMALL(IF(Schedule!$I$23:$I$125="Important",ROW(Schedule!$I$23:$I$125),306),ROW()-ROW($I$4)+1)-ROW($I$4)+1))

When the schedule was full, data was not being copied or was transferred to the wrong sheet.
I deleted most of the entries in order to start again but have found that my entries are not being copied or are returning nil/zeros

I am lost!
Any suggestions?
Thank you

Your second INDEX range is not the same size as the rest of the ranges:

=IF(ISERR(INDEX(Schedule!$I$23:$I$125,SMALL(IF(Schedule!$I$23:$I$125="Important",ROW(Schedule!$I$23:$I$125),306),ROW()-ROW($I$4)+1)-ROW($I$4)+1)),"",INDEX(Schedule!C$23:C$125,SMALL(IF(Schedule!$I$23:$I$125="Important",ROW(Schedule!$I$23:$I$125),306),ROW()-ROW($I$4)+1)-ROW($I$4)+1))

Also, make sure the formula is confirmed with CTRL+SHIFT+ENTER not just ENTER. You should see { } brackets appear around the formula if done correctly. Then copy formula down.

Hello,

Thank you so much for your time and your advice.
I have (hopefully) attached part of the workbook (I had to delete much to trim it to size- each person had a sheet and there were sheets for "completed" and "In Progress" also)
You will see that the section which you highlighted refers to the column on the relevant sheet and also that info is flying everywhere!

Any ideas with this extra insight please?

Which sheet am I looking in? What formula is giving a problem? What is the expected result in that formula?

Details should only be entered by supervisors as a log on the front page (Schedule)
The other sheets should then pick up on that detail using the formulas within them, to copy each row on the schedule and show it under the correct staff member sheet and also to the correct job priority sheet (eg, if the job has been allocated to "Andrew" and has been deemed as "important" then the row should be coped to Andrews sheet and also to the important sheet)
It was all working fine up until recently, but as you can see has now gone haywire and is copying things to the wrong sheet, if at all and returning some as nil values.
We suspect that a staff member has corrupted it (possibly by copying and pasting or inserting rows) and we are getting error warnings re circular references when we open the file up.

I so appreciate your help, thank you.

Try this formula in B4 of Andrew sheet:

=IFERROR(INDEX(Schedule!B$23:B$137,SMALL(IF((Schedule!$F$23:$F$137="Andrew")+(Schedule!$F$23:$F$137="All"),ROW(Schedule!$F$23:$F$137)-MIN(ROW(Schedule!$F$23:$F$137))+1),ROWS($B$4:$B4))),"")

confirmed with CTRL+SHIFT+ENTER not just ENTER. Copy down and across.

Is this what you are looking for?

Phenomenal! You are indeed a genius.
If only I could make the rows automatically size to fit their contents (both the schedule and sheets) then it would be perfect.
If you know how to achieve that, then I will have to think of an adjective which means "even more clever than a genius" with which to describe you!
Thank you so very much

You can create a Dynamic Named Range for the Schedule tab. This will allow you to add/remove rows without having to adjust the formulas that reference it.

Go to the Schedule sheet, then go to Formulas tab and select Define Name. Enter a name for the table, ScheduleTable

Then enter formula for the refers to field: =OFFSET(Schedule!$B$4,,,COUNTA(Schedule!$B:B)-3,8)

Now change the formula in Andrew, B4 that I gave you earlier to:

=IFERROR(INDEX(INDEX(ScheduleTable,0,COLUMNS($B$1:B$1)),SMALL(IF((INDEX(ScheduleTable,0,5)="Andrew")+(INDEX(ScheduleTable,0,5)="All"),ROW(ScheduleTable)-MIN(ROW(ScheduleTable))+1),ROWS($B$4:$B4))),"")

confirmed with CTRL+SHIFT+ENTER and copy across and down.

Repeat for other tabs changing the name "Andrew" as appropriate.

Many thanks, but it would not let me save that formula.
Your help and suggestions have been much appreciated nevertheless.

Not sure what you mean?

Make sure when you copy and paste the formula to your cell that you have not added a preceding space before the equal sign.

See attached. I applied the Dynamic Named Range, and the fetching formula to the Andrew sheet.

I am very sorry, but can not open that file. :-(
When I typed in your last formula, I had an invalid formula" message and I could not control, shift and enter.

Not sure why you can't open it. I just tried to open it from here and it worked.

What version of Excel are you using?

It won't work in 2003, but my first formula wouldn't have worked if you tried it in 2003 or earlier.

Hi,

I am so sorry, but I can not open that attachment.
I typed your formula as above, but there was some sort of error and it would not allow me to Control, Shift and enter.

I will keep trying.
Thank you for your help.

Excel 2013.
I don't think it is the computer; more like the operator!
I will keep trying.
Thank you