=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 we will ignore the Data table (A5:F10) and the Customer details (AV4:DV5)
The data we are after 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
Now we have moved to the Job Sheet Tab, this is the document that will print out when an order has been accepted.
So to recap our 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
So we have the PKId and the Heading, now we need to find the answer in the data table which we have named CustOrdOpt. We created this named field 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
Our answer is in the the 6th column of the table so we use 6 (in the formula) to get the value and FALSE means that it is an exact Match.
The final result would be 0900
I hope I have not confused you too much and if you have any questions about formula's in general I generally do a search in my preferred Internet browser and generally if you ask the right question you will find the help required.
If you get completely stuck feel free to contact support@buzsoftware.com.au but this may incur a fee for any customised formula's that are not already offered in the sheet.