Waste Transfer Notes: Solutions Document

Validation Tab

All text lists except for the Document number in column Q.

=VLOOKUP('Doc Template'!$D$6,Validation!$N:$O,2,FALSE)&TEXT('Doc Template'!D7,"0000")

2 parts to formula. Both reference the Doc Template tab

VLOOKUP('Doc Template'!$D$6,Validation!$N:$O,2,FALSE)
1. Look up the Select Site then reference columns N & O on Validation tab, return the 2^nd column.
  - Widnes = WPF-
TEXT('Doc Template'!D7,"0000")
1. Looks at the Select Number and converts it to a 4-character number.
  - 10 becomes 0010
Combined converts Widnes & 10 into “WPF-0010”

A screenshot of a spreadsheetAI-generated content may be incorrect.

Larger number provides future proofing of document into the thousands of route records.

Extract from Jet Report

Is extracting the ticker information from Business Central.

Main extract from BC for the ticket information uses this formula in cell F6.

=NL("Rows","Ticket",,"-Date",$C$2,"+Live Ticket No.","*","+Destination address name","*","+Haulier No.","STO007")

("Rows","Ticket"
1. Creates a row for each ticket extracted from BC.
",,"-Date",$C$2,"
1. Display the ticket in reverse date order: signified by the – sign. + would be assending.
2. $C$2 links to the date that the user specified when running the report.
  - >= 21.03.25 show all tickets that occur on or after 21^st Mar 2025.
"+Live Ticket No.","*",
1. Display the tickets in sequential order.
2. The “*” means all tickets.
.","*","+Destination address name","*","
1. Display the destination address in alphabetical order.
"+Haulier No.","STO007")
1. Only show tickets where the Haulier No = “STO007”

Referencing columns from the table in BC.

A close up of a boxAI-generated content may be incorrect.

=NF($F6,L$5)

$F6 is the ticker reference from BC. $F keeps the column on F.

L$5 is the column header “destination address name”. $5 keeps the row on 5.

Linking the formula to the cell above and using the actual name means you can enter the proper column names along the top of the extract and then drag this formula across.
1. Only having to type a column header once speeds up the process.
  - =NF($F6," Destination address name.")
  - =NF($F6,"SBPL Ticket No.")
  - =NF($F6,"System ID")

Column H: Unique Code =M6&N6&O6&P6&S6&R6

Works the same as concatenate.

On List: =IF(COUNTIF(Template[Code],$H6)<>0,"Y","N")

If the unique code for this ticket is not on the main Doc Template tab, returns N to prompt the user to add it.

EWC Blank: =IF(S6="",IF(COUNTIF(Validation!$Y:$Y,$W6)>0,"No","Yes"),"No")

The Countif looks at column Y on the Validation tab.

If the EWC Code from BC is blank, check if the item name has been added to column Y on the Validation tab. If it finds it, return “No”; if it doesn’t return Yes.

If the code is not blank, return No.

Source Add: =AB6&","&AC6&","&AD6&","&Y6

Del Add: =AE6&","&AF6&","&AG6&","&Z6

Generates a combined address record

Collection: Clifton,Penrith,Cumbria,CA10 2EY
Delivery: ,Workington,Cumbria,CA14 1JX

Last Used List: =IF(COUNTIF(LastUsed[Route],Extract!H6)=0,"No","Yes")

Like the EWC Blank check.

Checks the tab Last Used to see if the route is added to the table there.

If not, Flag No.

Last Used Tab

New Dates: =IFERROR(IF(B7="","",MAX(FILTER(Extract!$Q:$Q,Extract!$H:$H=B7))),0)

If B7 is blank, do nothing.
Otherwise, look for the highest date on the extract tab that matches the Route.
Outer brackets, IFERROR([Formula here],”Display when error”)
1. If it fails, it can cause lot of issues further on in the document.

Last Used: =IF(D7<>0,D7,C7)

If New Dates isn’t blank, use New Dates, otherwise use Prev Used.

Days: =IF([@[Last Used]]=0,400,TODAY()-E7)

If there is nothing in Last Used, set to 400 otherwise difference between today’s date and Last Used
Can be a negative number for ticket in the future.

Status: =IF([@Days]<=Validation!$S$3,"Hot",VLOOKUP([@Days],Validation!$S$3:$T$6,2,TRUE))

Look at “Hot” record on the Validation tab. If it’s less than that, currently 30, display hot. This is to bypass the issue of negative number.
Otherwise, lookup columns S & T on validation tab and pull back 2^nd column.
1. Using TRUE so it doesn’t have to be an exact match.

Route Status: =IFERROR(INDEX(Template[Status],MATCH([@Route],Template[Code],0)),"New Route")

Pull batch the route status from a matrix table.
Index (Match) allows multiple criteria in a better way than vlookup does.

Active/Inactive/New Routes:

=IFERROR(INDEX(Template[Active / Expired],MATCH([@Route],Template[Code],0)),"New Route")

Checks against the status on the Doc Template tab via the index match formula.

Check: =IF(AND([@[Active / Inactive / New Route]]="Expired",[@Status]="Hot"),"Movement on Expired Route",

IF(AND([@Status]="Dead",[@[Active / Inactive / New Route]]<>"Expired"),"Route no longer active",

"OK"))

2 checks completed on the route.

If status is HOT [recent movement] and the route is expired, Movement on expired route.

If status is DEAD [no movement >1Yr] and the route is expired, Route no longer active.

Add to List:

=IFERROR(SORT(UNIQUE(FILTER(Extract!$H:$H,(Extract!$AJ:$AJ="no")*(Extract!$J:$J="no")))),"None New")

Nested formula to provide a list of the new route only where this tab isn’t tracking their trading history.

IFERROR – As above; if there an error, display “None New”… main error here being no data to display.

SORT( sort ascending

Unique( only show each route code once

Filter( Show the code [column H] when ( Column AJ = No [not on Last Used Tab]) and Column J = No [EWC Blank check)

From the extract tab.
When multiple criteria checks are needed, put each check inside of brackets with a star ( * ) between them.

Note: this is an array formula which means the list is as long as it needs to be so without the need to drat any formula down. You need to make sure that NOTHING is added below this formula as it will stop it from working and kick out an error.

No EWC Tab

This pulls in the information for tickets without an EWC code where the item description has not been added to the exception list – See the validation tab.

There are essentially 2 different types of formulas on this tab.

No WEC Code

=IFERROR(SORT(UNIQUE(FILTER(Extract!$K:$K,(Extract!$J:$J="Yes")*(Extract!$AA:$AA=FALSE)))),"No Issues")

Like the above formulas on the Last Used tab, this is a nested formula to pulling back the unique ticket numbers [column K] when the EWC Blank check = Yes [column J] and the job isn’t cancelled [column AA].

Any errors (mainly that there is nothing to display) and it will display “no issues”.

This is an array formula and will extend down the tab only as much as it requires.

The others are variations on the following, just looking at different columns.

=IF(B3#="No Issues","",INDEX(Extract!$Q:$Q,MATCH($B3#,Extract!$K:$K,0)))

Just change the “Extract!$Q:$Q” to match the column that you want to retrieve data from.

The import part of this formula is the $B3#

The # allows a connection to the array formula and means that this formula will also extend as far as required without the need to have them copied down the tab and always active.

Additional Tabs

The remain 4 tabs, Permit Details / Broker&Dealer Details / Haulier Details / Site Permit Details are just reference tabs without formulas attached.

These need to be maintained by the users.

New Routes Tab

This is the tab where the users add their information to the new routes before copying it over to the main Doc Template.

Column B has the new routes.

=IFERROR(SORT(UNIQUE(FILTER(Extract!$H:$H,(Extract!$I:$I="N")))),"None New")

Similar array formula to those above and pull in the unique route codes when column I on the Extract tab (On List Check) = N.

Returns “None New” when there are no new routes.

Above this is a count of the new routes that the team need to review.

=IF(B10="None New",0,COUNTIF(B10#,"<>"&""))

When the above returns “None New”, show 0.

Otherwise, count B10#, the # allowing it to interact with the array, <> [is not] “” [blank].

Each site has a check of the highest WTN number on the Doc Template.

=MAX(IF(Template[Site Prefix]=E2,Template[[No ]]))

This looks at the Site Prefix on the Doc Template table and returns the highest value in the [No ] (number) column.

This allows the team to remove unrequired WTN’s without causing any duplication of numbers for each site.

The table employs several elements.

A screenshot of a computerAI-generated content may be incorrect.

The Count column only starts a count when the Site Prefix is populated.

=COUNTIF($E$10:$E10,$E10)

The first portion is $E$10:$E10.
1. The second E10 does not have the $ on the row to allow it to move down the page and only count up to the row it is on, instead of the whole column.

Site Prefix: this is list link via the Data Validation option to display the options listed on the Validation tab.

A screenshot of a computerAI-generated content may be incorrect.

In Allow select List, then in the Source select the column with the options from the Validation tab.

No column.

=IFERROR(VLOOKUP([@[Site Prefix]],$E$2:$F$7,2,FALSE)+[@Count],"-")

The IFERROR formula will display a “-“ when a site isn’t selected.

After that the formula is looking up the Site Prefix to the highest WTN number at the top of the tab and adding the Count column to give a number for this new route.

Our Ref No

=[@[Site Prefix]]&TEXT([@[No ]],"0000")

Combination of the Site Prefix with the No column in the 4-digit format.

For the rest of the table.

Anything on the rest of the table is that highlighted yellow requires action from the user.

These are either free type fields, such as the Start Date and How Much Waste? Columns, or they are drop down menu’s linked to other tabs.

Note: All formulas are copied to row 7 where possible as a backup in case they are tampered with.

End Date

=IF(NewRoutes[@[Start Date]]="","",NewRoutes[@[Start Date]]+365)

Displays “-“ until the Start Date is populated and then added 365 days to the date.

Waste Description / EWC Code / Source Name & Address.

=IFERROR(INDEX(Extract!$W:$W,MATCH([@Code],Extract!$H:$H,0)),"")

All link to the Extract tab pulling back the required information.

Adjust the Extract!$W:$W to change the column that the formula is pulling back.

SIC Code: This is defaulted to 38210.

="38210"

The “ “ around the number make sure that it viewed as TEXT instead of number so when more rows are added, it does not add +1 to each row,

Permit Details: Links to the “Permit Details” tab.

Permit No / Issues by / Waste Exemption Reg / Details

=IFERROR(VLOOKUP([@[Source Name]],'Permit Details'!$B:$D,3,FALSE),"Add to Permit Details")

Looks up the Source Name from the table to reference the Permit Details tab.

The Permit No field returns the value “Add to Permit Details” if it does not find the source on that tab.

The other tabs display “ “.

=IFERROR(VLOOKUP([@[Source Name]],'Permit Details'!$B:$E,4,FALSE),"")

The number 3 before the FALSE is the column that is returned from the Permit Details.

This is adjusted to pull back the required column.

Signature of Holder: Links to the Print Name field but has the format changed to look more like a signature.

=[@[Print Name]]

Format Cells applied of General;;

A screenshot of a computerAI-generated content may be incorrect.

Custom format allows for different formatting based on whether the value is positive, negative or blank. These are separated by the semi colon.

General is applied to the positive values.

By just added the ; twice, the sheet is told to display [nothing] unless the value is positive which in this case means “no blank”.

Broker Details

The Name is drop down menu from the Broker&Dealer Details tab.

Likewise the Address / PC (Post Code) / Reg fields are a vlookup to the same tab.

=IFERROR(VLOOKUP([@[Broker Name]],'Broker&Dealer Details'!$B:$F,2,FALSE),"Add Broker Details")

As above, the first column displays “Add Broker Details” when there is nothing added to the Name field but the other display nothing.

This works for the same for the Dealer details.

Carrier Full Name is defaulted to Seras as the extract is only looking for tickets with the Haulier Code STO007.

="Seras"

The next 6 columns continue to reference the Haulier Details tab.

=IFERROR(VLOOKUP([@[Carrier Full Name]],'Haulier Details'!$B:$H,2,FALSE),"Add Carrier Details")

Destination Details – Pulls from the Extract tab.

=IFERROR(INDEX(Extract!$L:$L,MATCH([@Code],Extract!$H:$H,0)),"")

The next 6 columns pull the information back from the Site Permit Details tab based on the information in the Destination Name field.

Doc Template Tab

Most of the formulas & drop-down menus are identical to the New Routes tab and the users are encouraged to copy & paste values from that tab into this one.

If needed, the formulas are added to row 3 of this tab so they can be added to the top row & copied down.

A yellow box with black textAI-generated content may be incorrect.

This is used for creating the WTN’s.

Select Site: Drop down menu to the Validation tab.

Sites are names here to make it easier for the users instead of selecting WPF-

Select Number: Manually entry for the user.

Considering linking to this table but then then the enter 0010 they can’t just type 10.

Status: =IFERROR(INDEX(Template[Status],MATCH(Validation!$Q$3,Template[Our Ref No],0)),"Not on list")

Reference the table on this tab so they can see immediately if the document needs to be reviewed before the print.

Additional Functions not on the New Routes Tab: See the right hand side.

A screenshot of a computerAI-generated content may be incorrect.

Active / Expired: =IF([@[End Date]]<TODAY(),"Expired","Active")

Simple if statement. If End Date column is less than today, Expired else Active.

Status: Drop down menu linked to the Validation tab.

A white grid with black textAI-generated content may be incorrect.

Counter for each “review” status to aid the team.

=COUNTIF(Template[Status],AV6)

Countif ( Status column on table, name on the left-hand column).

Close Lane

Yes, No List.

Has Strikethrough attached via Conditional Formatting for Closed Lanes.

This will be covered at the end of this section.

Check: Vlookup to the Last Used tab.

=IFERROR(VLOOKUP([@Code],LastUsed,9,FALSE),"")

Last Used: lookup to last used date on the Last Used tab.

=IFERROR(VLOOKUP([@Code],LastUsed[[Route]:[Last Used]],4,FALSE),"")

End Check: If no End Data added will display nothing. Otherwise, difference between the end date & today.

Negative number is out of date.

=IF([@[End Date]]="","",[@[End Date]]-TODAY())

End Status: Updates based on the End Check.

=IFERROR(IF([@[Close Lane]]="Yes","Closed", IF([@[End check]]<0,"Out of Date", VLOOKUP([@[End check]],Validation!$V:$W,2,TRUE))),"Check")

If the lane is closed, display closed.

If the End Check if < zero, display out of date.

Lookup the End check value on the Validation tab to give the status.

Using TRUE to find the closest value.

Return “Check” if there are any issues.

A white grid with black textAI-generated content may be incorrect.

Count of each for users to review.

Duplicate

=IF(COUNTIF([Code],[@Code])>1,"Yes","")

=IF(COUNTIFS([Code],[@Code],[Site Prefix],[@[Site Prefix]])>1,"Yes","")

2 different review types for users.

1^st one: duplicate of route code.

2^nd one: duplicate of roue code & site prefix.

Document Tab

Generation of the WTN based on the selected value on the Doc Template tab.

Only 3 types of formulas used here.

Date Period at the top of the tab.

=TEXT(VLOOKUP(L8,Template[[Our Ref No]:[End Date]],3,FALSE),"DD/MM/YYYY")&" to "&TEXT(VLOOKUP(L8,Template[[Our Ref No]:[End Date]],4,FALSE),"DD/MM/YYYY")

Displays 3 items.

First Part: TEXT(VLOOKUP(L8,Template[[Our Ref No]:[End Date]],3,FALSE),"DD/MM/YYYY")

Start date in the format DD/MM/YYYY

Second Part: &” to “&

& allows you to combined multiple formula.

“ to “ is adding a space, the word “to” and another space between the two dates.

Third Part: TEXT(VLOOKUP(L8,Template[[Our Ref No]:[End Date]],4,FALSE),"DD/MM/YYYY")

End date in the format DD/MM/YYYY

Reference No

=Validation!Q3

Reference the combined code in cell Q3 on the validation tab.

=VLOOKUP('Doc Template'!$D$6,Validation!$N:$O,2,FALSE)&TEXT('Doc Template'!D7,"0000")

Reference from the Document Creation section on the Doc Template.

E.g. WPF-0010

Document Information

=IFERROR(INDEX(Template[Source Name],MATCH($L$8,Template[Our Ref No],0)),"Check")

References the Doc Template table based on the Reference No at the top of the tab.