Help with FILTER & SPILL and formatting...

MeaclH · Dec 12, 2024

Hi all,

I have a sheet I am working on that has a formula working perfectly. It cross checks the values in the column versus values required on a different sheet. For any values that aren't there, they are returned and spilt into the cells below. This is perfect.

However, as part of our work flow, when we have verified that the value is not required, I have a VBA that marks the value as crossed out. You will see the formatting change in column S5 is now plain black text as opposed to the bold red in others.
The problem I have, is the values in the column are changed regularly, so once I have changed the formatting of S5, and another value is removed from the column, that value is put in the top row, and pushes the others ones down. The formatting remains in S5 rather than sticking with the value.

I don't think I have explained that very well. I hope you can follow what I am asking for

Essentialy, I'd like to keep the formatting with the value, rather than the cell. Or if you have any other suggestions on how to reflect the values that have been cross checked.

Formula in cell

=FILTER(DATA!$AM$3:$AM$19,NOT(COUNTIF(S11:S103,DATA!$AM$3:$AM$19)))

VBA used to format cells we have double checked.

VBA Code:

Sub MarkShiftCovered()
     Dim sCmt As String
    Dim rCell As Range
    
    sCmt = InputBox( _
      Prompt:="Have you covered this shift in it's entirety?" & vbCrLf & _
      "Please add details of how the shift has been covered. ie. OFF roster, extensions.", _
      Title:="Comment to Add")
    If sCmt = "" Then
        MsgBox "Not actioned. Has the shift been covered?"
    If StrPtr(sCmt) = 0 Then Exit Sub 'user cancelled inputbox
            Else
        For Each rCell In Selection
            With rCell
                ActiveSheet.Unprotect
                .ClearComments
                .AddComment
                .Comment.Text Text:=sCmt
            End With
        Next
    End If
    Set rCell = Nothing
Selection.Font.Color = RGB(0, 0, 0)
Selection.Interior.ColorIndex = xlNone
Selection.Font.Underline = False
Selection.Font.Bold = False
Selection.Font.Strikethrough = True
ActiveSheet.Protect DrawingObjects:=False
End Sub

DIGITAL ROSTER MACH2.xlsm

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

1

2

FORTNIGHT COMMENCING

UNPUBLISHED

SUNDAY

MONDAY

TUESDAY

WEDNESDAY

THURSDAY

FRIDAY

SATURDAY

SUNDAY

MONDAY

TUESDAY

WEDNESDAY

THURSDAY

FRIDAY

SATURDAY

3

4

01-Dec-24

Employee Name

Open Shifts

5

#CALC!

0530TL

1330TL

6

1330TL

7

8

9

10

DAO Team Leaders

11

Line 1

Rostered T/L

DONMEZ. K

Shift

0530TL

OFF

0530TL

12

Original T/L

DONMEZ. K

Extension

13

Remarks

####

Remarks

14

Line 2

Rostered T/L

CUGLIARI. D

Shift

1330TL

OFF

1330TL

OFF

15

Original T/L

CUGLIARI. D

Extension

16

Remarks

9

Remarks

17

Line 3

Rostered T/L

CROCE. B

Shift

OFF

1330TL

2130TL

OFF-U

A/L

18

Original T/L

CROCE. B

Extension

19

Remarks

9

Remarks

20

Line 4

Rostered T/L

ABDULRAHMAN. M

Shift

2130TL

OFF

2130TL

21

Original T/L

ABDULRAHMAN. M

Extension

22

Remarks

9

Remarks

23

Line 5

Rostered T/L

PAPARIS. M

Shift

OFF

0700AD

0530TL

OFF

1330TL

1200AD

OFF

24

Original T/L

PAPARIS. M

Extension

25

Remarks

9

Remarks

FN 01DEC-14DEC24

Cell Formulas
Range	Formula
G3	G3	=IF(A4="","",A4)
H3:T3	H3	=IF(G3="","",G3+1)
G5,M5:N5,T5	G5	=FILTER(DATA!$AL$3:$AL$17,NOT(COUNTIF(G11:G103,DATA!$AL$3:$AL$17)))
H5:L5,O5:R5,S5:S6	H5	=FILTER(DATA!$AM$3:$AM$19,NOT(COUNTIF(H11:H103,DATA!$AM$3:$AM$19)))
C11,C23,C20,C17,C14	C11	=IF($A$4="","",VLOOKUP(CONCATENATE(VALUE($A$4),$A11),DATA!$U$3:$V$1568,2,0))
C12,C24,C21,C18,C15	C12	=IF($A$4="","",VLOOKUP(CONCATENATE(VALUE($A$4),$A11),DATA!$U$3:$V$1568,2,0))
E13,E25,E22,E19,E16	E13	=AR11

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G:G,H:H,I:I,J:J,K:K,L:L,M:M,N:N,O:O,P:P,Q:Q,R:R,S:S,T:T	Expression	=G$3=TODAY()	text	NO
G15:T15,G18:T18,G21:T21,G24:T24,G28:T28,G31:T31,G34:T34,G37:T37,G40:T40,G43:T43,G46:T46,G49:T49,G52:T52,G55:T55,G58:T58,G61:T61,G64:T64,G67:T67,G70:T70,G73:T73,G76:T76,G79:T79,G82:T82,G85:T85,G89:T89,G92:T92,G95:T95,G98:T98,G101:T101,G105:T105,G108:T108	Cell Value	ending with "?"	text	NO
G15:T15,G18:T18,G21:T21,G24:T24,G28:T28,G31:T31,G34:T34,G37:T37,G40:T40,G43:T43,G46:T46,G49:T49,G52:T52,G55:T55,G58:T58,G61:T61,G64:T64,G67:T67,G70:T70,G73:T73,G76:T76,G79:T79,G82:T82,G85:T85,G89:T89,G92:T92,G95:T95,G98:T98,G101:T101,G105:T105,G108:T108	Cell Value	contains "OK"	text	NO
G15:T15,G18:T18,G21:T21,G24:T24,G28:T28,G31:T31,G34:T34,G37:T37,G40:T40,G43:T43,G46:T46,G49:T49,G52:T52,G55:T55,G58:T58,G61:T61,G64:T64,G67:T67,G70:T70,G73:T73,G76:T76,G79:T79,G82:T82,G85:T85,G89:T89,G92:T92,G95:T95,G98:T98,G101:T101,G105:T105,G108:T108	Cell Value	contains "DEC"	text	NO
G27:T86,G88:T102,G104:T118,G14:T25,G11:T11,G12,H12:T13	Cell Value	contains "GAZETTE"	text	NO
G27:T86,G88:T102,G104:T118,G14:T25,G11:T11,G12,H12:T13	Cell Value	contains "AV"	text	NO
G27:T86,G88:T102,G104:T118,G14:T25,G11:T11,G12,H12:T13	Cell Value	contains "EDO-U"	text	NO
G27:T86,G88:T102,G104:T118,G14:T25,G11:T11,G12,H12:T13	Cell Value	contains "OFF-U"	text	NO
G27:T86,G88:T102,G104:T118,G14:T25,G11:T11,G12,H12:T13	Cell Value	contains "BLV"	text	NO
G27:T86,G88:T102,G104:T118,G14:T25,G11:T11,G12,H12:T13	Cell Value	contains "CTFN"	text	NO
G27:T86,G88:T102,G104:T118,G14:T25,G11:T11,G12,H12:T13	Cell Value	contains "CDO"	text	NO
G27:T86,G88:T102,G104:T118,G14:T25,G11:T11,G12,H12:T13	Cell Value	contains "STFN"	text	NO
G27:T86,G88:T102,G104:T118,G14:T25,G11:T11,G12,H12:T13	Cell Value	contains "SDO"	text	NO
G27:T86,G88:T102,G104:T118,G14:T25,G11:T11,G12,H12:T13	Cell Value	contains "LSL"	text	NO
G27:T86,G88:T102,G104:T118,G14:T25,G11:T11,G12,H12:T13	Cell Value	contains "A/L"	text	NO
G27:T86,G88:T102,G104:T118,G14:T25,G11:T11,G12,H12:T13	Cell Value	contains "PHC"	text	NO
G27:T86,G88:T102,G104:T118,G14:T25,G11:T11,G12,H12:T13	Cell Value	contains "OFF"	text	NO
G27:T86,G88:T102,G104:T118,G14:T25,G11:T11,G12,H12:T13	Cell Value	contains "EDO"	text	NO
G5:T5	Cell	contains an error	text	NO
E13,E16,E19,E22,E25	Cell Value	>=12	text	NO
E13,E16,E19,E22,E25	Cell Value	=11	text	NO
E13,E16,E19,E22,E25	Cell Value	=10	text	NO
E13,E16,E19,E22,E25	Cell Value	=9	text	NO
C2	Cell Value	contains "UNPUBLISHED"	text	NO
C2	Cell Value	contains "PUBLISHED"	text	NO
C12,C15,C18,C21,C24,C28,C43,C58,C73,C31,C46,C61,C76,C34,C49,C64,C79,C37,C52,C67,C82,C40,C55,C70,C85	Expression	=IF(C12=C11,TRUE)	text	NO

Cells with Data Validation
Cell	Allow	Criteria
C11:E11	List	=DATA!$AB$4:$AB$12
C14:E14	List	=DATA!$AB$4:$AB$12
C17:E17	List	=DATA!$AB$4:$AB$12
C20:E20	List	=DATA!$AB$4:$AB$12
C23:E23	List	=DATA!$AB$4:$AB$12
G17:T17	List	=DATA!$AF$4:$AF$49
G11:T11	List	=DATA!$AF$4:$AF$49
G14:T14	List	=DATA!$AF$4:$AF$49
G20:T20	List	=DATA!$AF$4:$AF$49
G23:T23	List	=DATA!$AF$4:$AF$49
C2:E3	List	=DATA!$AJ$4:$AJ$5
A4:B9	List	=DATA!$W$4:$W$70

zero269 · Dec 29, 2024

MeaclH said:
The problem I have, is the values in the column are changed regularly, so once I have changed the formatting of S5, and another value is removed from the column, that value is put in the top row, and pushes the others ones down. The formatting remains in S5 rather than sticking with the value.

Formatting:
I think using Conditional Formatting Rules may be best suited for your needs regarding the cell formatting.
To prevent empty/blank cells from formatting, add this rule to the top for your entire data range and Stop If True is checked.

Spills:
Is it supposed to spill below your DAO Team Leaders row? I noticed that row is MERGED as well. That can cause Spill errors. Try Centering Across Selection for the Alignment instead.

I just noticed your Dates and I think I have something that you'll like better... I'll be back.

zero269 · Dec 29, 2024

For the Dates, try this in a test Sheet. Enter your Start Date in A4 like you have it here.
It will SPILL to the RIGHT using TRANSPOSE for the Dates row.
If you want the Weekdays full name, change "ddd" to "dddd".
I added IF and ISBLANK incase there is no date in A4, else it will start with a ZERO date.

VBA Testing.xlsm

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

AH

AI

AJ

AK

2

SUN

MON

TUE

WED

THU

FRI

SAT

SUN

MON

TUE

WED

THU

FRI

SAT

SUN

MON

TUE

WED

THU

FRI

SAT

SUN

MON

TUE

WED

THU

FRI

SAT

SUN

MON

TUE

3

1-Dec

2-Dec

3-Dec

4-Dec

5-Dec

6-Dec

7-Dec

8-Dec

9-Dec

10-Dec

11-Dec

12-Dec

13-Dec

14-Dec

15-Dec

16-Dec

17-Dec

18-Dec

19-Dec

20-Dec

21-Dec

22-Dec

23-Dec

24-Dec

25-Dec

26-Dec

27-Dec

28-Dec

29-Dec

30-Dec

31-Dec

Filter-Spill

Cell Formulas
Range	Formula
G2:AK2	G2	=UPPER(TEXT(G3#,"ddd"))
G3:AK3	G3	=IF(ISBLANK(A4),"",TRANSPOSE(SEQUENCE(DAY(EOMONTH(A4,0)),1,A4)))
Dynamic array formulas.

MeaclH · Monday at 2:10 PM

zero269 said:
For the Dates, try this in a test Sheet. Enter your Start Date in A4 like you have it here.
It will SPILL to the RIGHT using TRANSPOSE for the Dates row.
If you want the Weekdays full name, change "ddd" to "dddd".
I added IF and ISBLANK incase there is no date in A4, else it will start with a ZERO date.

VBA Testing.xlsm
G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH AI AJ AK
2 SUN MON TUE WED THU FRI SAT SUN MON TUE WED THU FRI SAT SUN MON TUE WED THU FRI SAT SUN MON TUE WED THU FRI SAT SUN MON TUE
3 1-Dec 2-Dec 3-Dec 4-Dec 5-Dec 6-Dec 7-Dec 8-Dec 9-Dec 10-Dec 11-Dec 12-Dec 13-Dec 14-Dec 15-Dec 16-Dec 17-Dec 18-Dec 19-Dec 20-Dec 21-Dec 22-Dec 23-Dec 24-Dec 25-Dec 26-Dec 27-Dec 28-Dec 29-Dec 30-Dec 31-Dec
Filter-Spill
Cell Formulas
Range Formula
G2:AK2 G2 =UPPER(TEXT(G3#,"ddd"))
G3:AK3 G3 =IF(ISBLANK(A4),"",TRANSPOSE(SEQUENCE(DAY(EOMONTH(A4,0)),1,A4)))
Dynamic array formulas.

Hey Zero!
Sorry for the delay replying, have been on holidays. This is a handy improvement, thank you!

1 thing about it though, is the formula spills an entire months worth of dates, however my sheet only works in fortnights. Is there a way for it to spill only 14 dates based on A4 value.

Cheers
Hayden

MeaclH · Monday at 2:12 PM

zero269 said:
Formatting:
I think using Conditional Formatting Rules may be best suited for your needs regarding the cell formatting.
To prevent empty/blank cells from formatting, add this rule to the top for your entire data range and Stop If True is checked.

View attachment 120739

Spills:
Is it supposed to spill below your DAO Team Leaders row? I noticed that row is MERGED as well. That can cause Spill errors. Try Centering Across Selection for the Alignment instead.

View attachment 120737
I just noticed your Dates and I think I have something that you'll like better... I'll be back.

Thanks again for these tips. Yeah, the formatting is a little tricky. Not sure if there is a fix for my problem, I might have to figure out another way to process the changes we make.

As for the spilling, it is very unlikely there would be that number of values that would cause it to spill below.
Cheers!

Help with FILTER & SPILL and formatting...

MeaclH

Board Regular

Excel Facts

zero269

Active Member

zero269

Active Member

MeaclH

Board Regular

MeaclH

Board Regular

Similar threads

Forum statistics

Share this page

Help with FILTER & SPILL and formatting...

MeaclH

Board Regular

Excel Facts

zero269

Active Member

zero269

Active Member

MeaclH

Board Regular

MeaclH

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock