Hi there,
Can you please help me find a way to filter data so that it appears in different sections that pertains to two people?
You will see in my sample, that I want it to find text that begins with 'S -' and shows it on the right hand side, further to this, I want it to also filter text that begins with 'J - ' and add it to the right hand side below the first.
Thank you in advance.
Can you please help me find a way to filter data so that it appears in different sections that pertains to two people?
You will see in my sample, that I want it to find text that begins with 'S -' and shows it on the right hand side, further to this, I want it to also filter text that begins with 'J - ' and add it to the right hand side below the first.
Thank you in advance.
| Book1-2.xlsx | |||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | |||
| 3 | (S) | ||||||||||||||||||||
| 4 | Fortnight 1 | Fortnight 2 | |||||||||||||||||||
| 5 | Phone | $ 34.00 | Spotify | $ 20.99 | |||||||||||||||||
| 6 | S - Phone - $34.00 | S - Debt - $30.00 | S - Power - $100 | Debt | $ 30.00 | Debt | $ 30.00 | ||||||||||||||
| 7 | J - Phone - $34.00 | J - Amazon - $9.99 | S - School - $60 | Power | $ 100.00 | Power | $ 100.00 | ||||||||||||||
| 8 | J - Mortgage - $350.00 | School | $ 60.00 | School | $ 60.00 | ||||||||||||||||
| 9 | Netflix | $ 10.99 | |||||||||||||||||||
| 10 | PHI | $ 72.00 | |||||||||||||||||||
| 11 | |||||||||||||||||||||
| 12 | |||||||||||||||||||||
| 13 | S - Netflix - $10.99 | S - PHI - $72.00 | |||||||||||||||||||
| 14 | |||||||||||||||||||||
| 15 | |||||||||||||||||||||
| 16 | Total Needed | Total Needed | |||||||||||||||||||
| 17 | $306.99 | $210.99 | |||||||||||||||||||
| 18 | |||||||||||||||||||||
| 19 | |||||||||||||||||||||
| 20 | S - Spotify - $20.99 | S - Debt - $30.00 | S - Power - $100 | (J) | |||||||||||||||||
| 21 | S - School - $60 | Fortnight 1 | Fortnight 2 | ||||||||||||||||||
| 22 | Phone | $ 34.00 | CSA | $ 200.00 | |||||||||||||||||
| 23 | Audible | $ 9.99 | Mortgage | $ 350.00 | |||||||||||||||||
| 24 | Mortgage | $ 350.00 | |||||||||||||||||||
| 25 | |||||||||||||||||||||
| 26 | |||||||||||||||||||||
| 27 | J - CSA - $200.00 | J - Mortgage - $350.00 | |||||||||||||||||||
| 28 | |||||||||||||||||||||
Payments Calendar | |||||||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| T17,V17 | T17 | =SUM(U5:U15) |
| Cells with Conditional Formatting | ||||
|---|---|---|---|---|
| Cell | Condition | Cell Format | Stop If True | |
| N7 | Cell Value | contains "S -" | text | NO |
| F7 | Cell Value | contains "S -" | text | NO |
| N20:N21 | Cell Value | contains "S -" | text | NO |
| E6 | Expression | =$E6=your_condition | text | NO |
| Q11 | Expression | =MonthToDisplayNumber<>MONTH(Q11) | text | NO |
| Q11 | Expression | =MonthToDisplayNumber<>MONTH(Q11) | text | NO |
| Q11 | Expression | =MonthToDisplayNumber<>MONTH(Q11) | text | NO |
| O11 | Expression | =MonthToDisplayNumber<>MONTH(O11) | text | NO |
| O11 | Expression | =MonthToDisplayNumber<>MONTH(O11) | text | NO |
| O11 | Expression | =MonthToDisplayNumber<>MONTH(O11) | text | NO |
| M11 | Expression | =MonthToDisplayNumber<>MONTH(M11) | text | NO |
| M11 | Expression | =MonthToDisplayNumber<>MONTH(M11) | text | NO |
| M11 | Expression | =MonthToDisplayNumber<>MONTH(M11) | text | NO |
| K11 | Expression | =MonthToDisplayNumber<>MONTH(K11) | text | NO |
| K11 | Expression | =MonthToDisplayNumber<>MONTH(K11) | text | NO |
| K11 | Expression | =MonthToDisplayNumber<>MONTH(K11) | text | NO |
| I11 | Expression | =MonthToDisplayNumber<>MONTH(I11) | text | NO |
| I11 | Expression | =MonthToDisplayNumber<>MONTH(I11) | text | NO |
| I11 | Expression | =MonthToDisplayNumber<>MONTH(I11) | text | NO |
| G11 | Expression | =MonthToDisplayNumber<>MONTH(G11) | text | NO |
| G11 | Expression | =MonthToDisplayNumber<>MONTH(G11) | text | NO |
| G11 | Expression | =MonthToDisplayNumber<>MONTH(G11) | text | NO |
| E11 | Expression | =MonthToDisplayNumber<>MONTH(E11) | text | NO |
| E11 | Expression | =MonthToDisplayNumber<>MONTH(E11) | text | NO |
| E11 | Expression | =MonthToDisplayNumber<>MONTH(E11) | text | NO |
| E11,G11,I11,K11,M11,O11,Q11 | Expression | =MonthToDisplayNumber<>MONTH(E11) | text | NO |
| E11,G11,I11,K11,M11,O11,Q11 | Expression | =MonthToDisplayNumber<>MONTH(E11) | text | NO |
| E11,G11,I11,K11:M11,O11,Q11 | Expression | =MonthToDisplayNumber<>MONTH(E11) | text | NO |
| E11:R11 | Cell Value | contains "S -" | text | NO |
| E11,Q11,G11,I11,K11,M11,O11 | Expression | =MonthToDisplayNumber<>MONTH(E11) | text | NO |
| G11,I11,K11:M11,O11,Q11,E11 | Expression | =MonthToDisplayNumber<>MONTH(E11) | text | NO |
| E11:R11,E18:R18,E25:R25,E32:R32,E39:R39,E46:H46 | Cell Value | contains "★" | text | NO |
| E11,G11,I11,K11:M11,O11,Q11 | Cell Value | contains "Payday" | text | NO |
| G11,I11,K11:M11,O11,Q11,E11 | Expression | =E11=TODAY() | text | NO |
| I11,M11,O11,Q11,G11,E11,K11 | Expression | =MonthToDisplayNumber<>MONTH(E11) | text | NO |
| Q18 | Expression | =MonthToDisplayNumber<>MONTH(Q18) | text | NO |
| Q18 | Expression | =MonthToDisplayNumber<>MONTH(Q18) | text | NO |
| Q18 | Expression | =MonthToDisplayNumber<>MONTH(Q18) | text | NO |
| O18 | Expression | =MonthToDisplayNumber<>MONTH(O18) | text | NO |
| O18 | Expression | =MonthToDisplayNumber<>MONTH(O18) | text | NO |
| O18 | Expression | =MonthToDisplayNumber<>MONTH(O18) | text | NO |
| M18 | Expression | =MonthToDisplayNumber<>MONTH(M18) | text | NO |
| M18 | Expression | =MonthToDisplayNumber<>MONTH(M18) | text | NO |
| M18 | Expression | =MonthToDisplayNumber<>MONTH(M18) | text | NO |
| K18 | Expression | =MonthToDisplayNumber<>MONTH(K18) | text | NO |
| K18 | Expression | =MonthToDisplayNumber<>MONTH(K18) | text | NO |
| K18 | Expression | =MonthToDisplayNumber<>MONTH(K18) | text | NO |
| I18 | Expression | =MonthToDisplayNumber<>MONTH(I18) | text | NO |
| I18 | Expression | =MonthToDisplayNumber<>MONTH(I18) | text | NO |
| I18 | Expression | =MonthToDisplayNumber<>MONTH(I18) | text | NO |
| G18 | Expression | =MonthToDisplayNumber<>MONTH(G18) | text | NO |
| G18 | Expression | =MonthToDisplayNumber<>MONTH(G18) | text | NO |
| G18 | Expression | =MonthToDisplayNumber<>MONTH(G18) | text | NO |
| E18 | Expression | =MonthToDisplayNumber<>MONTH(E18) | text | NO |
| E18 | Expression | =MonthToDisplayNumber<>MONTH(E18) | text | NO |
| E18 | Expression | =MonthToDisplayNumber<>MONTH(E18) | text | NO |
| E18,G18,I18,K18,M18,O18,Q18 | Expression | =MonthToDisplayNumber<>MONTH(E18) | text | NO |
| E18,G18,I18,K18,M18,O18,Q18 | Expression | =MonthToDisplayNumber<>MONTH(E18) | text | NO |
| E18,G18,I18,K18:M18,O18,Q18 | Expression | =MonthToDisplayNumber<>MONTH(E18) | text | NO |
| E18:R18 | Cell Value | contains "S -" | text | NO |
| E18,Q18,G18,I18,K18,M18,O18 | Expression | =MonthToDisplayNumber<>MONTH(E18) | text | NO |
| G18,I18,K18:M18,O18,Q18,E18 | Expression | =MonthToDisplayNumber<>MONTH(E18) | text | NO |
| E18:R18 | Cell Value | contains "★" | text | NO |
| E18,G18,I18,K18:M18,O18,Q18 | Cell Value | contains "Payday" | text | NO |
| G18,I18,K18:M18,O18,Q18,E18 | Expression | =E18=TODAY() | text | NO |
| I18,M18,O18,Q18,G18,E18,K18 | Expression | =MonthToDisplayNumber<>MONTH(E18) | text | NO |
| Q25 | Expression | =MonthToDisplayNumber<>MONTH(Q25) | text | NO |
| Q25 | Expression | =MonthToDisplayNumber<>MONTH(Q25) | text | NO |
| Q25 | Expression | =MonthToDisplayNumber<>MONTH(Q25) | text | NO |
| O25 | Expression | =MonthToDisplayNumber<>MONTH(O25) | text | NO |
| O25 | Expression | =MonthToDisplayNumber<>MONTH(O25) | text | NO |
| O25 | Expression | =MonthToDisplayNumber<>MONTH(O25) | text | NO |
| M25 | Expression | =MonthToDisplayNumber<>MONTH(M25) | text | NO |
| M25 | Expression | =MonthToDisplayNumber<>MONTH(M25) | text | NO |
| M25 | Expression | =MonthToDisplayNumber<>MONTH(M25) | text | NO |
| K25 | Expression | =MonthToDisplayNumber<>MONTH(K25) | text | NO |
| K25 | Expression | =MonthToDisplayNumber<>MONTH(K25) | text | NO |
| K25 | Expression | =MonthToDisplayNumber<>MONTH(K25) | text | NO |
| I25 | Expression | =MonthToDisplayNumber<>MONTH(I25) | text | NO |
| I25 | Expression | =MonthToDisplayNumber<>MONTH(I25) | text | NO |
| I25 | Expression | =MonthToDisplayNumber<>MONTH(I25) | text | NO |
| G25 | Expression | =MonthToDisplayNumber<>MONTH(G25) | text | NO |
| G25 | Expression | =MonthToDisplayNumber<>MONTH(G25) | text | NO |
| G25 | Expression | =MonthToDisplayNumber<>MONTH(G25) | text | NO |
| E25 | Expression | =MonthToDisplayNumber<>MONTH(E25) | text | NO |
| E25 | Expression | =MonthToDisplayNumber<>MONTH(E25) | text | NO |
| E25 | Expression | =MonthToDisplayNumber<>MONTH(E25) | text | NO |
| E25,G25,I25,K25,M25,O25,Q25 | Expression | =MonthToDisplayNumber<>MONTH(E25) | text | NO |
| E25,G25,I25,K25,M25,O25,Q25 | Expression | =MonthToDisplayNumber<>MONTH(E25) | text | NO |
| E25,G25,I25,K25:M25,O25,Q25 | Expression | =MonthToDisplayNumber<>MONTH(E25) | text | NO |
| E13:R17,E41:H46,E8:R10,F6:R6,E6:E7,G7:M7,E22:R25,E20:M21,O20:R21,O7:R7,E35:R39,E34:K34,M34:R34,E27:R32 | Cell Value | contains "S -" | text | NO |
| G5 | Expression | =G5=TODAY() | text | NO |
| G5 | Expression | =MonthToDisplayNumber<>MONTH(G5) | text | NO |
| E6:E7 | Cell Value | contains "Payday" | text | NO |
| E6:E7 | Expression | =E6=TODAY() | text | NO |
| E6:E7 | Expression | =MonthToDisplayNumber<>MONTH(E6) | text | NO |
| E8:E10 | Cell Value | contains "Payday" | text | NO |
| E8:E10 | Expression | =E8=TODAY() | text | NO |
| E8:E10 | Expression | =MonthToDisplayNumber<>MONTH(E8) | text | NO |
| E25,Q25,G25,I25,K25,M25,O25 | Expression | =MonthToDisplayNumber<>MONTH(E25) | text | NO |
| G25,I25,K25:M25,O25,Q25,E25 | Expression | =MonthToDisplayNumber<>MONTH(E25) | text | NO |
| F5,H5,J5,L5,L12,N12,N5,P5,P12,R12,R5,J12,H12,F12,F19,H19,J19,L19,N19,P19,R19,R33,R26,P26,P33,N33,N26,L26,L33,J33,H33,H26,J26,F26,F33 | Cell Value | contains "Payday" | text | NO |
| R12 | Expression | =R12=TODAY() | text | NO |
| R12 | Expression | =MonthToDisplayNumber<>MONTH(R12) | text | NO |
| P12 | Cell Value | contains "Payday" | text | NO |
| P12 | Expression | =P12=TODAY() | text | NO |
| P12 | Expression | =MonthToDisplayNumber<>MONTH(P12) | text | NO |
| N12 | Cell Value | contains "Payday" | text | NO |
| N12 | Expression | =N12=TODAY() | text | NO |
| N12 | Expression | =MonthToDisplayNumber<>MONTH(N12) | text | NO |
| L12 | Cell Value | contains "Payday" | text | NO |
| L12 | Expression | =L12=TODAY() | text | NO |
| L12 | Expression | =MonthToDisplayNumber<>MONTH(L12) | text | NO |
| J12 | Cell Value | contains "Payday" | text | NO |
| J12 | Expression | =J12=TODAY() | text | NO |
| J12 | Expression | =MonthToDisplayNumber<>MONTH(J12) | text | NO |
| E32:R32,E39:R39,E25:R25 | Cell Value | contains "Birthday" | text | NO |
| E5 | Expression | =E5=TODAY() | text | NO |
| E5 | Expression | =MonthToDisplayNumber<>MONTH(E5) | text | NO |
| E19:R19,E26:R26,K20:K25,M20:M25,E33:R33,K27:K30,E12:I12,M27:M32,G13:G17,I13:I17,G6:G10,I6:I10,M6:M10,O6:O10,Q6:Q10,E27:E32,E13:E17,K6:K10,G27:G32,I27:I32,K32:L32,O27:O32,Q27:Q32,E34:E39,G34:G39,I34:I39,K34:K39,M34:M39,O34:O39,Q34:Q39,E20:E25,G21:G25,I20:I25 | Cell Value | contains "Payday" | text | NO |
| F5,F19:R19,F26:R26,K5:R5,K20:K25,M20:M25,F33:R33,K27:K30,H5:I5,F12:I12,M27:M32,G13:G17,I13:I17,G6:G10,I6:I10,M6:M10,O6:O10,Q6:Q10,K6:K10,G27:G32,I27:I32,K32:L32,O27:O32,Q27:Q32,G34:G39,I34:I39,K34:K39,M34:M39,O34:O39,Q34:Q39,G21:G25,I20:I25,L25,O20:O25 | Expression | =E5=TODAY() | text | NO |
| E27,G27,I27,K27,M27,O27,Q27 | Expression | =MonthToDisplayNumber<>MONTH(E27) | text | NO |
| P19:Q19 | Expression | =P19=TODAY() | text | NO |
| M28:M31,E28:E32,G28:G32,I28:I32,K32:M32,O28:O32,Q28:Q32 | Expression | =MonthToDisplayNumber<>MONTH(E28) | text | NO |
| O8:O10 | Expression | =MonthToDisplayNumber<>MONTH(O8) | text | NO |
| F5,E33:R33,F19:R19,F26:R26,K5:R5,I13:I17,G13:G17,O6:O7,I20:I25,M20:M25,O20:O25,Q20:Q25,K28:K30,K32,I34,M34,K34,O34,Q34,H5:I5,I6:I10,G6:G10,F12:I12,M6:M10,O12:O17,Q6:Q10,G21:G25,E12:E17,K6:K10,K20:K25,E19:E26,K12:K17,Q12:Q17,M12:M17 | Expression | =MonthToDisplayNumber<>MONTH(E5) | text | NO |
| K34:K38,M34:M38,O34:O38,Q34:Q38,I34:I38,G34:G38,E34:E38,E27:E31,G27:G31,I27:I31,K27:K31,M27:M31,O27:O31,Q27:Q31,M20:M24,O20:O24,Q20:Q24,K20:K24,I20:I24,G20:G24,E20:E24,E13:E17,E6:E10,G6:G10,I6:I10,I13:I17,G13:G17,K13:K17,M13:M17,O13:O17,Q13:Q17,Q6:Q10 | Cell Value | contains "S -" | text | NO |
