Is this possible? Table of data to different sheets

monkeyharris

Active Member
Joined
Jan 20, 2008
Messages
368
Office Version
  1. 365
Platform
  1. 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.

1655669292971.png


Booking Schedule1 - Copy.xlsm
ABCDEFGHIJKLMNO
1CustomerPostcodeAreaOrder NumDate ReqVehicle TypeTruck RegTruck SizeRunNotesDate BookedUser IDBooked ByAssigned SitePallets
2C G ReynoldsW2 4UALondon599622121/06/22ArticFA16XMMArtic No Moffett1st10:30 Delivery13/06/22EL20Laura C0Full Load
3Chelmsford PlasticCM1 3EHChelmsford599653621/06/22ArticFM16LSUArtic No Moffett2nd16/06/22EL4NayeemEast LondonArtic
4Plastering ContractorsSW8 5BBLondon599394521/06/22AnyFA16XMC32t CS No Moffett1st08:00 Delivery15/06/22EL32Daisy0Full Load
5CLM FireproofingSW8 5BBLondon599555421/06/22CS MoffettFA16XMC32t CS No Moffett2nd13:00 Delivery13/06/22EL13Karen03
6Plastering ContractorsSW8 5BBLondon599638721/06/22AnyFA16XMC32t CS No Moffett2nd13:00 Delivery16/06/22EL32Daisy03
7Just DesignsW1T 3EYLondon599651821/06/22CS MoffettFJ66YSF32t CS Moffett1st1st Drop16/06/22EL17Steve V02
8GBS FireNW8 8RTLondon599662121/06/22AnyFJ66YSF32t CS Moffett1st1st Drop17/06/22EL4Nayeem0Nothing
9FitzgeraldN11 1HJLondon599654721/06/22AnyFJ66YSF32t CS Moffett1st1st Drop16/06/22EL4Nayeem05
10Platt & ReillyN1C 4BELondon599647921/06/22AnyFJ66YSF32t CS Moffett1st1st Drop16/06/22EL36DanielEast London1
11RELN8 0NWLondon599642221/06/22AnyFJ66YSF32t CS Moffett1st1st Drop15/06/22EL17Steve VEast London0.5
12RELN8 0NWLondon599661721/06/22AnyFJ66YSF32t CS Moffett1st1st Drop17/06/22EL17Steve VEast LondonNothing
13KBS BuildersSE7 8LHLondon599653321/06/22CS MoffettFC16MPY32t CS Moffett1st16/06/22EL4NayeemEast LondonFull 32t
14Chelmsford PlasticCM1 3AGChelmsford599653521/06/22AnyFC16MPY32t CS Moffett2nd16/06/22EL4NayeemEast London5
15Workplace InteriersCO16 0LQClacton-On-Sea599654821/06/22AnyFC16MPY32t CS Moffett2nd16/06/22EL17Steve VEast London0.5
16Silverton AggregatesCO4 3EFColchester599653121/06/22AnyFC16MPY32t CS Moffett2nd16/06/22EL4NayeemEast London2
17OCL DryliningTW18 4AEStaines-Upon-Thames599654421/06/22AnyFA16XME32t CS Moffett2nd14:00 Delivery16/06/22EL32Daisy011x1.2m
18Lee MarleySE15 6JJLondon599641621/06/22CS MoffettFA16XME32t CS Moffett1st15/06/22EL14Katy04.5
19Skyline RoofingSE4 2DSLondon599540921/06/22AnyFA16XME32t CS Moffett1st30/05/22EL20Laura C03
20MagnetEN1 1SPEnfield599652921/06/22AnyFC16MRU32t CS Moffett1st16/06/22EL4NayeemEast London1
21Carter LaurenEN8 0TAWaltham Cross599652721/06/22CS MoffettFC16MRU32t CS Moffett1st16/06/22EL4Nayeem01
22Mems DIYN17 6PYLondon599653421/06/22AnyFC16MRU32t CS Moffett1st16/06/22EL4NayeemEast London1
23Mems DIYN17 6PYLondon599661021/06/22AnyFC16MRU32t CS Moffett1st17/06/22EL14KatyEast London2
24Insulation ShopN17 9ENLondon599656121/06/22AnyFC16MRU32t CS Moffett1st17/06/22EL4NayeemEast London1.5
25Tim KSE2 9SGLondon599608821/06/22FlatbedST64BZH26t FB No Moffett2nd12:00 Delivery17/06/22EL36DanielEast London3x2.7, 3x2.4
21st
Cell Formulas
RangeFormula
M2:M25M2=IF(L2="","",VLOOKUP(L2,'Vehicle Info'!$D$38:$E$51,2))
N2:N25N2=IF(B2="","",VLOOKUP(B2,Branches!$A$2:$K$2919,3))
C2:C25C2=IF(B2="","",VLOOKUP(B2,Branches!$A$2:$K$2919,11))
H2:H25H2=IF(G2="","",VLOOKUP(G2,'Vehicle Info'!$A$37:$B$52,2))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D1Cell ValueduplicatestextNO
 

Attachments

  • 1655669034459.png
    1655669034459.png
    7 KB · Views: 4

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Here is a copy of the sheet where the information will copy into. A to D 11, A to D12, A to D13 etc.

Booking Schedule1 - Copy.xlsm
ABCDEF
2DATEVEHICLEDRIVERMILEAGE
321/06/2022REG NO:BD65YSKWAYNESTART
4MOBILE:07842315950FINISH
5PRE - ROUTE DAILY CHECKS
6VEHICLE DAILY DEFECT INSPECTION REPORT PPE RELEVANT AND IN GOOD CONDITION
7LOADS CHECKED FOR ACCURACY DRIVERS NAME & REG ON EACH DELIVERY NOTE
8LOADS CHECKED FOR EVEN DISTRIBUTION, SUITABLE POSITIONING AND MATERIAL IS SECURED SECURE CURTAINS, DOORS & MOFFETT
9RUN / DELVIERIES
10CUSTOMERTICKET NO.POSTCODEDELIVERY TIMEON SITE OFF SITE
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
YSK
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A11:D20Expression=$T$1="VOR"textNO
C11:C20Cell ValueduplicatestextNO
A21:D22Expression=$T$1="VOR"textNO
C21:C22Cell ValueduplicatestextNO
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top