Common formulas used in BUZ

= Cell reference

By pressing = then selecting a cell this will show the value of a cell (that may be in another tab) in the cell you require

Where you see Data! or ‘Job Sheet’! in the formula like:
=Data!BF5 or ='Job Sheet'!B12 or =C12

Means the data is being referenced or copied from another sheet/tab. In the above examples, Data! refers to the Data sheet and ‘Job Sheet’! refers to the Job Sheet tab

Generally all of your information will come from the data tab, unless you have deductions or reference tables, but we will get to that later.

=Data!BF5

='Job Sheet'!B12

=C12

=Cell1&"."&Cell2

This will add 2 cell values together with a . between them

=Data!BC5&"."&Data!BD5

=CONCATENATE(Cell1,".",Cell2)

Essentially the same as above but works on older versions of excel

=CONCATENATE(Data!BC5,".",Data!BD5)

https://exceljet.net/functions/concatenate-function

=TRIM(Cell1)

TRIM function returns a text value with the leading and trailing spaces removed, i.e. if there are spaces before and after your data, these will be removed.

=TRIM(Data!AX5)

https://exceljet.net/functions/trim-function

=bpLOOKUP()

Essentially a Vlookup with an extra variable for the search

For a detailed explanation Click here

=bpLookup(Data!AV11,D$12,CustOrdOpt,6,False)

=PROPER(cell reference)

 Capitalises the first letter in a text string/words

 =PROPER(C3)

Open

https://exceljet.net/functions/proper-function

=MID(Cell reference,start number,amount of characters after the start number)

 Returns a specific number of characters from a text string/words, starting at the position and number of characters you specify

=MID(A2,2,3)

Open

https://exceljet.net/functions/mid-function

=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match

 When you need to find things in a table or a range by row. E.g. look up a value from your deductions table

=VLOOKUP(D12,Deductions,2,False)

https://exceljet.net/functions/vlookup-function

=MATCH(value you want to look up, table array where to look, match type)

 Searches for a specified item in a range of cells, and then returns the relative position of that item in the range.
Match Types:
1 or omitted - finds the larges value that is less than or equal to the look up value.
0 - Minds the first value that is exactly equal to the look up value
-1 - Finds the smallest value that is greater than or equal to the look up value

https://exceljet.net/functions/match-function

=IF

 IF statement can have two results. The first result is if your comparison is True, the second if your comparison is False

=IF(D12=”CHAIN”,”Yes”,”No”)

https://exceljet.net/functions/if-function

=IF(ISNUMBER(SEARCH("-",B2)),"E","")

If a cell contains a character return a value

Example formula on the left, if cell B2 contains a - (dash), then show E in the cell that the formula is being written in.

If cell B2 does not contain an E then this cell will be blank

https://exceljet.net/functions/search-function

=LEFT(B1,LEN(B1)-1)

Extract all Characters Except the Last Character Using Formula

=LEFT(B1,LEN(B1)-1)

https://www.excelhow.net/how-to-extract-all-characters-except-the-first-or-last-character-from-text-string-in-excel.html