...
...
There is 2 common formula’s that we use to get the data into the table
The first is a simple “display cell contents in another cell” or =Ref
=IF(ISBLANK(Data!BP12),"",Data!BP12)
That is used to get the data in the area highlighted in the red square, generally information that relates to the Main inventory item (Materials) and any thing that uses a Keyword in the group options (ITEMWIDTH, QTY, DESCN)
...
Second Formula option - the Lookup
Lookups are used to return the answers from the group options
...
I know those pictures are hard to see but there is a lot of data in the tab, when you have your jobsheet open it will be easier to see all the data.
Lookup example
For this example I have added a question “Opening or Make” to my group options and now I want to insert this into my jobsheet.
...
I have added a new column and in row 10 (which is normally hidden) I input the HEADING which aligns with the Group Options.
Then I copied the formula into the cell =bpLOOKUP(Data!$AV11,G$10,CustOrdOpt,6,FALSE)
...
One thing to note, do you see the G$10 that is in bold,
...
Make sure that is referencing the HEADING, my Heading is “SIZES” just for your information but your column might not be in G10 so make sure they align
Then you can copy paste the formula all the way down
So where is this information coming from?
This is the data tab and in yellow is a table called “CustOrdOpt” or Customer Order Options all the group option headings and answers are here
...
In red is the ID for the answers, which help us see which product we are getting the information from
and in Blue is the HEADINGS and Answers
...
Hopefully that all works for you
Job Sheet Considerations
...
Common Formula's used in BUZ
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 | |
For help relating to othe pages that can be added to a job sheet click on one of the links below
...
=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 |