Page 1 of 2 1 2 LastLast
Results 1 to 10 of 14

Thread: Please help with a demon workbook!!!

  1. #1

    Please help with a demon workbook!!!



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    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.


  3. #3
    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?
    Attached Files Attached Files

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    Which sheet am I looking in? What formula is giving a problem? What is the expected result in that formula?


  5. #5
    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.

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    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?


  7. #7
    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

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    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.


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

  10. #10
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,518
    Articles
    0
    Excel Version
    Excel 2016
    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.
    Attached Files Attached Files


Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •