Job Sheet Considerations
...
Formula | Explaination | Example |
|---|---|---|
=Cell | 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! in the fomula like =Data!BF5 Mean that the cell value is being copied from the Data 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 |
=Cell1&"."&Cell2 | this will add 2 cells 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) |
=TRIM(Cell1) | TRIM function returns a text value with the leading and trailing spaces removed. | =TRIM(Data!AX5) |
=bpLOOKUP() | Essentially a Vlookup with an extra variable for the search For a detailed explanation Click here | |
...
=PROPER(cell reference) |  |
=MID(Cell reference,start number,amount of characters after the start number) =MID(H14,6,99) | |
=VLOOKUP | |
=MATCH | |
=IF | |
=IF(ISNUMBER(SEARCH("-",B2)),"E","") | If a cell contains a character return a value For the example on the left if cell B2 contains a - then show E in the cell that the formula is being written If cell B2 does not contain an E then this cell will be blank |
=LEFT(B1,FIND(“|”,B1)-1) | =RIGHT(B1,LEN(B1)-FIND("_",B1)) | ||
A | B | C | |
1 | Chain|OPCHAIN | Chain | OPCHAIN |
2 | Motor 3.3v|MTR33 | Motor 3.3v | MTR33 |
3 | Motor Solar|MTRSOL | Motor Solar | MTRSOL |