Maintenance Schedule Checklist

I am trying setup a maintenance schedule checklist for a large list of items. Each item has a cycle in weeks for the checklist. I set up a nested if statement to check if the item is overdue or done based on the cycle and the current date. The if statement checks when the last time the item was done or overdue and populates an "X" for the next due date. It works fine as long as the cycle is 4 or less. After that there are too many if statements and I get an error. I'd rather not have to use a macro to get this to work. Any ideas?

Perhaps the below would work for you ?

Please Login or Register  to view this content.

The can be applied across your matrix G2:Q51
I replicated your results with the above - and calculated > 4 week cycle.

Note: uses OFFSET which is Volatile... you could use INDEX but it would be a little lengthier - let us know if this is what you want to do.

I pondered on this for a while and concluded that a revised approach was in order... see the attached (sheet DOTE). I have inserted a new Row 1 in which there is a Column index identifier... this is so as to be able to avoid using Volatile function COLUMN in the LOOKUP function used in the subsequent results table itself.

As such the *new* formula in G3 (old G2) becomes:

=IF(AND(F3<>"OVERDUE",F$1>0,COLUMNS($G2:G2)-LOOKUP(10^10,CHOOSE({1,2},0,LOOKUP(2,1/ISNUMBER(MATCH($F3:F3,{"DONE","X"},0)),$F$1:F$1)))<$B3),"",IF(TODAY()-5>G$2,"OVERDUE","X"))

This is applied across the entire table.

I *think* this works...

It should not be putting an X if every cell thereafter only wherever the next cycle is due... my sample file demonstrates that doesn't it ?

Are you saying you should only ever have 1 X in a row at any one time -- that is to say the cycles should never repeat ?

Originally Posted by matthemj2

Or I would like it to show only the X's of when it is due based on the cycle. For example, if I put done in cell H4, cycle of 2 weeks, then I4 is blank and J4 is X, as it should be. But K4 and all the cells in that row thereafter have an X. Based on what I want it to do K4 should be blank, L4 should have an X, M4 should be blank and so on.

I'm beyond confused ... in the file I provided if you put DONE in H4 you would get the following:

I4: Blank
J4: X
K4: Blank
L4: X
M4: Blank
N4: X
O4: Blank
P4: X
Q4: Blank

Using your own recent attachment and adding DONE to H4 I get the above results too.

Please post a file where you have entered DONE into H4 and are getting these results you state as I can not replicate.

In Post #4 I attached my entire file - I can't do much else I'm afraid.

2003, no

Settings... unlikely.

Please attach the file you used to generate your screenshot, I can deduce nothing from the screenshot other than it doesn't work.

I have to confess I am stumped...the attached shows what I see when I open your latest file (no changes made):

Is there any VBA in your file -- there was none in the one you posted here, just curious.

I take it back ... something in there does not like running pre2007 ... I'm guessing the LOOKUP element... I will get back to you... profound apologies as I should have tested myself.

OK Dokey... the issue is as expected to do with how the ISNUMBER(MATCH with inline array constant executes in 2007 versus earlier versions... I've never come across that issue before so I've learnt something today.

To resolve...

The following formula in G3 (copied across matrix) should generate desired results:

Please Login or Register  to view this content.

No problem.

I consider the issue arising from this to be VERY interesting ... I can not determine why it should work in 2007 but not in earlier versions given the functions used are entirely backwards compatible... in this context the issue *appears* to relate to how 2007 handles precedents versus earlier versions but I confess that I don't have the requisite knowledge to state this categorically and so I've taken the matter up with some others who may be able to confirm the true cause one way or another, unfortunately I can not provide a link to this discussion, however, I will post back here as and when I have useful feedback.

Seems very random, DonkeyOte...

I'd be interested to see what your contacts say too.

I looked at a few to compare results... didn't catch anything that should cause that in one cell and not another with same formula...

NBVC, I'm convinced that the issue centres around this part of the formulae and the handling of precedents etc...

LOOKUP(2,1/ISNUMBER(MATCH($F3:P3,{"DONE","X"},0)),$F$1:P$1)

In 2007 the Output of the overall formulae in the referenced cells ARE recognised whereas earlier versions seemingly do not recognise the results of the same cells - ie the above only ever returns an error pre 2007 ... hard to explain... what I would suggest if you're interested is to open the last file I posted in both a pre2007 and 2007 instance, if you analyse the above component across columns G:Q in a separate row you will see what I mean (hopefully).