Template Details #TheBayWip #DetailsProduct
The details product worksheet is where most of the main calculation take place for the summary chart. It also makes up most of the data hosted in this worksheet process. The calculation that guiding the rows are fairly complex, and the formulas needed to be flexible and future proof. The users of this worksheet will require to understand its basic structure before tweaking the template.
The components of this worksheet is divided into the following parts:
- Special Rows
- Important Regions that follows specific rules
- Data source sheet
The data source sheet is hosting most of the raw data read from the input file. The default name of the worksheet is called "Looker_data". The reference row(defaulted to row 4) stores all the corresponding column names from the data source sheet on the details-product worksheet. This row helps the automation script to find the correct columns to copy over and base its calculations on.
The names of reference row must match the exact spelling and case of the column names located on the "Looker_data" worksheet.
Specials Rows and Important Regions will be discussed below.
There are few special rows to consider in all the details product worksheet for all banners. TheBay banner has following special rows:
- Formula Row (3rd row by default)
- Reference Row (4th row by default)
- Header Row (7th row by default)
The formula row include formulas that can be customized for calculations. The automation script will apply formulas inside formula to the column directly below to every row up to the end of data region. Therefore the user has the freedom to customize any calculation they wish, the functionality resembles "dragging down" of formula row.
Additionally, there are specialized keywords embedded inside the Formula row to improve readability. For full list, please refer to Formula Row Keyword List.
The reference row contains the name of columns the data sheet of details product suppose to process from. The names of each column has to be matched exactly, else a blank column will be produced.
The official "Title" row, this row is used as reference for writing data and applications of excel formulas.
When changing the template, there is the special region that exists in the worksheet which the order of columns must be preserved. This is due to the implementation of the special rule for TheBay banner, it requires tuning of the re-work status from "Copy" department. This region can be moved together as bundle of 5 columns, otherwise there are not other restrictions on changing the template.
This region is 5 columns wide which include "Rework_Status", "Rollup_Complete", "Workflow Exception Type", "Current_Workflow_Status", and "Current Team".
This region's data will always be values only, as the calculations require the data to be values only in the intermediate steps, formulas will not be preserved.
The details product page formula row have special keyword that encapsulate an operation to improve readability of the template. The keyword replaced old excel worksheet formulas that are both clunky and slow.
For the full descriptions, please refer to Keyword List.