Job sheet - Explaining the bpLOOKUP
- Alex Morrisson
- Allison Derome (Unlicensed)
=bpLOOKUP(Data!$AV11,D$12,CustOrdOpt,6,FALSE)) |
|---|
Breakdown of the formula into its individual components.
| Formula | Explanation |
|---|---|
| = | Denotes that a formula is active |
| bpLOOKUP | Part of a macro written by BUZ but essentially like a vlookup to a named field in the data page |
| Data!$AV11 | Lookup Value (product Primary Key Identification reference) |
| D$12 | Lookup Value – (the Heading you are searching for) |
| CustOrdOpt | Table array - (area you are searching) |
| 6 | Col-index-num - Selecting the cell number in a table |
| FALSE)) | Range lookup - FALSE = Exact Match (If you use TRUE it may not be accurate) |
To better explain the formula refer to this scenario.
Using the formula to find the Width of the first product in the job sheet. The information is from the Data Tab and it will show it in the table in the job sheet Tab.
| =bpLOOKUP( | Data!$AV11,D$12,CustOrdOpt,6,FALSE)) |
|---|
Step 1 is telling the Cell what type of formula you are using, in this case is it a modified Vlookup which uses a few parameters to find you an answer
=bpLOOKUP( | Data!$AV11 | ,D$12,CustOrdOpt,6,FALSE)) |
|---|
Step 2 selecting the correct product line for the formula.
Below is a snap shot of the "Data Tab" in a job sheet containing 3 products (or line items)
For this exercise ignore the Data table (A5:F10) and the Customer details (AV4:DV5)
The data to look at is Cells AV11:CK57.
Each line represents the information relating to a product
- AV11 is product 1
- AV12 is product 2 and
- AV13 is product 3.
Each product has it's own PkId (Primary Key Identification) number. So the formula above is looking for the PKId for a product
| The answer so far would show | ca7cfa99-b34d-4cc9-b39e-f09fc2fd01d3 |
=bpLOOKUP(Data!$AV11, | D$12, | CustOrdOpt,6,FALSE)) |
|---|
Step 3
Move to the Job Sheet Tab, this is the document that will print out when an order has been accepted.
So to recap the formula is doing a look up, first it is getting the PKId (AV11) and now we need to narrow which part of the product we are looking for.
In this scenario we are looking for the Width of the product and named the Heading ITEMWIDTH (which is highlighted in Yellow).
| The answer so far would show | ca7cfa99-b34d-4cc9-b39e-f09fc2fd01d3 |
| ITEMWIDTH |
=bpLOOKUP(Data!$AV11,D$12, | CustOrdOpt, | 6,FALSE)) |
|---|
Step 4
Next is to find the answer in the data table which BUZ has named CustOrdOpt. Buz created this named field to simplify the process.
In the image below is the CustOrdOpt Table I have highlighted each product a different colour.
- Product 1 is Yellow
- Product 2 is Blue
- Product 3 is Green
- Feel free to ignore the orange area, it has a use but not in this exercise.
So now that we have the PKId code and the Heading.
| The answer so far would show | ca7cfa99-b34d-4cc9-b39e-f09fc2fd01d3 |
| ITEMWIDTH |
We search the CustOrdOpt table for the row we are looking for, which would find the answer in row 13
=bpLOOKUP(Data!$AV11,D$12,CustOrdOpt, | 6,FALSE)) |
|---|
Step 5
Selecting the answer in the row. The answer is in the the 6th column of the table, use 6 (in the formula) to get the value and FALSE means that it is an exact Match.
The final result would be 0900
If you have any questions about formula's in general, do a search in your preferred Internet browser and ask the right question to find the help required.
If you get stuck contact support@buzsoftware.com.au (this may incur a fee for any customised formula's that are not already offered in the sheet).