- Joined
- Dec 16, 2012
- Messages
- 2,319
- Reaction score
- 40
- Points
- 48
- Excel Version(s)
- 365
I'm guessing you're doing something like (you'll have to qualify the ranges and or set them yourself since I haven't seen your code)
for the top table and perhaps using the same formula for the lower table, however, you could alter the formula a bit for the lower table:
Now there shouldn't be a problem with blanks not being found in the lower table (unless all relief drivers for the day are resting - in which case, as I said before, come back).
In the SPARE column, you could have something specifying that that driver is NOT available, otherwise leave it blank, then use
above .value =.value for the lower table.
And by the way, this last suggestion would be a good way of taking a relief driver out of the equation if he's on semi-permanent duty - just put, say, Perm in the SPARE column against his name.
Code:
With Range("D3:J40")
.FormulaR1C1 = "=IF(VLOOKUP(RC3,'Rest Days'!C1:C8,R201C[7],FALSE)=0,RC2,""Rest"")"
.Value = .Value
End With
Code:
With Range("D44:J72")
.FormulaR1C1 = "=IF(VLOOKUP(RC3,'Rest Days'!C1:C8,R201C[7],FALSE)=0,[COLOR=#ff0000]""""[/COLOR],""Rest"")"
.Value = .Value
End With
In the SPARE column, you could have something specifying that that driver is NOT available, otherwise leave it blank, then use
Code:
.FormulaR1C1 = "=IF(VLOOKUP(RC3,'Rest Days'!C1:C8,R201C[7],FALSE)=0,IF(RC2="""","""",RC2),""Rest"")"
And by the way, this last suggestion would be a good way of taking a relief driver out of the equation if he's on semi-permanent duty - just put, say, Perm in the SPARE column against his name.
Last edited: