monkeyharris
Active Member
- Joined
- Jan 20, 2008
- Messages
- 368
- Office Version
- 365
- Platform
- Windows
Hi all,
I'm not sure if Excel is the correct program to use, but i hope so as i love it.
I have a table of data with column "G" being the key lookup value. Is there a way i can add a macro so that when you press a button, it moves all lines with the same vehicle registration to pre-determined sheets. I.E the image below shows those sheet names. So in the main table, all lines showing FJ66YSF in column G will populate in sheet names YSF.
The sheets names YSK, YYE, HYB etc will remain static. This will be a monthly workbook so sheets will be dated from 1st through to month end day, i need the button to extract the data from the active sheet and populate the YSK, YYE, HYB sheets.
I really hope this is possible.
I'm not sure if Excel is the correct program to use, but i hope so as i love it.
I have a table of data with column "G" being the key lookup value. Is there a way i can add a macro so that when you press a button, it moves all lines with the same vehicle registration to pre-determined sheets. I.E the image below shows those sheet names. So in the main table, all lines showing FJ66YSF in column G will populate in sheet names YSF.
The sheets names YSK, YYE, HYB etc will remain static. This will be a monthly workbook so sheets will be dated from 1st through to month end day, i need the button to extract the data from the active sheet and populate the YSK, YYE, HYB sheets.
I really hope this is possible.
Booking Schedule1 - Copy.xlsm | |||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
1 | Customer | Postcode | Area | Order Num | Date Req | Vehicle Type | Truck Reg | Truck Size | Run | Notes | Date Booked | User ID | Booked By | Assigned Site | Pallets | ||
2 | C G Reynolds | W2 4UA | London | 5996221 | 21/06/22 | Artic | FA16XMM | Artic No Moffett | 1st | 10:30 Delivery | 13/06/22 | EL20 | Laura C | 0 | Full Load | ||
3 | Chelmsford Plastic | CM1 3EH | Chelmsford | 5996536 | 21/06/22 | Artic | FM16LSU | Artic No Moffett | 2nd | 16/06/22 | EL4 | Nayeem | East London | Artic | |||
4 | Plastering Contractors | SW8 5BB | London | 5993945 | 21/06/22 | Any | FA16XMC | 32t CS No Moffett | 1st | 08:00 Delivery | 15/06/22 | EL32 | Daisy | 0 | Full Load | ||
5 | CLM Fireproofing | SW8 5BB | London | 5995554 | 21/06/22 | CS Moffett | FA16XMC | 32t CS No Moffett | 2nd | 13:00 Delivery | 13/06/22 | EL13 | Karen | 0 | 3 | ||
6 | Plastering Contractors | SW8 5BB | London | 5996387 | 21/06/22 | Any | FA16XMC | 32t CS No Moffett | 2nd | 13:00 Delivery | 16/06/22 | EL32 | Daisy | 0 | 3 | ||
7 | Just Designs | W1T 3EY | London | 5996518 | 21/06/22 | CS Moffett | FJ66YSF | 32t CS Moffett | 1st | 1st Drop | 16/06/22 | EL17 | Steve V | 0 | 2 | ||
8 | GBS Fire | NW8 8RT | London | 5996621 | 21/06/22 | Any | FJ66YSF | 32t CS Moffett | 1st | 1st Drop | 17/06/22 | EL4 | Nayeem | 0 | Nothing | ||
9 | Fitzgerald | N11 1HJ | London | 5996547 | 21/06/22 | Any | FJ66YSF | 32t CS Moffett | 1st | 1st Drop | 16/06/22 | EL4 | Nayeem | 0 | 5 | ||
10 | Platt & Reilly | N1C 4BE | London | 5996479 | 21/06/22 | Any | FJ66YSF | 32t CS Moffett | 1st | 1st Drop | 16/06/22 | EL36 | Daniel | East London | 1 | ||
11 | REL | N8 0NW | London | 5996422 | 21/06/22 | Any | FJ66YSF | 32t CS Moffett | 1st | 1st Drop | 15/06/22 | EL17 | Steve V | East London | 0.5 | ||
12 | REL | N8 0NW | London | 5996617 | 21/06/22 | Any | FJ66YSF | 32t CS Moffett | 1st | 1st Drop | 17/06/22 | EL17 | Steve V | East London | Nothing | ||
13 | KBS Builders | SE7 8LH | London | 5996533 | 21/06/22 | CS Moffett | FC16MPY | 32t CS Moffett | 1st | 16/06/22 | EL4 | Nayeem | East London | Full 32t | |||
14 | Chelmsford Plastic | CM1 3AG | Chelmsford | 5996535 | 21/06/22 | Any | FC16MPY | 32t CS Moffett | 2nd | 16/06/22 | EL4 | Nayeem | East London | 5 | |||
15 | Workplace Interiers | CO16 0LQ | Clacton-On-Sea | 5996548 | 21/06/22 | Any | FC16MPY | 32t CS Moffett | 2nd | 16/06/22 | EL17 | Steve V | East London | 0.5 | |||
16 | Silverton Aggregates | CO4 3EF | Colchester | 5996531 | 21/06/22 | Any | FC16MPY | 32t CS Moffett | 2nd | 16/06/22 | EL4 | Nayeem | East London | 2 | |||
17 | OCL Drylining | TW18 4AE | Staines-Upon-Thames | 5996544 | 21/06/22 | Any | FA16XME | 32t CS Moffett | 2nd | 14:00 Delivery | 16/06/22 | EL32 | Daisy | 0 | 11x1.2m | ||
18 | Lee Marley | SE15 6JJ | London | 5996416 | 21/06/22 | CS Moffett | FA16XME | 32t CS Moffett | 1st | 15/06/22 | EL14 | Katy | 0 | 4.5 | |||
19 | Skyline Roofing | SE4 2DS | London | 5995409 | 21/06/22 | Any | FA16XME | 32t CS Moffett | 1st | 30/05/22 | EL20 | Laura C | 0 | 3 | |||
20 | Magnet | EN1 1SP | Enfield | 5996529 | 21/06/22 | Any | FC16MRU | 32t CS Moffett | 1st | 16/06/22 | EL4 | Nayeem | East London | 1 | |||
21 | Carter Lauren | EN8 0TA | Waltham Cross | 5996527 | 21/06/22 | CS Moffett | FC16MRU | 32t CS Moffett | 1st | 16/06/22 | EL4 | Nayeem | 0 | 1 | |||
22 | Mems DIY | N17 6PY | London | 5996534 | 21/06/22 | Any | FC16MRU | 32t CS Moffett | 1st | 16/06/22 | EL4 | Nayeem | East London | 1 | |||
23 | Mems DIY | N17 6PY | London | 5996610 | 21/06/22 | Any | FC16MRU | 32t CS Moffett | 1st | 17/06/22 | EL14 | Katy | East London | 2 | |||
24 | Insulation Shop | N17 9EN | London | 5996561 | 21/06/22 | Any | FC16MRU | 32t CS Moffett | 1st | 17/06/22 | EL4 | Nayeem | East London | 1.5 | |||
25 | Tim K | SE2 9SG | London | 5996088 | 21/06/22 | Flatbed | ST64BZH | 26t FB No Moffett | 2nd | 12:00 Delivery | 17/06/22 | EL36 | Daniel | East London | 3x2.7, 3x2.4 | ||
21st |
Cell Formulas | ||
---|---|---|
Range | Formula | |
M2:M25 | M2 | =IF(L2="","",VLOOKUP(L2,'Vehicle Info'!$D$38:$E$51,2)) |
N2:N25 | N2 | =IF(B2="","",VLOOKUP(B2,Branches!$A$2:$K$2919,3)) |
C2:C25 | C2 | =IF(B2="","",VLOOKUP(B2,Branches!$A$2:$K$2919,11)) |
H2:H25 | H2 | =IF(G2="","",VLOOKUP(G2,'Vehicle Info'!$A$37:$B$52,2)) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
D1 | Cell Value | duplicates | text | NO |