It is also called as "Manage related coloumns"
This functionality helps to relate column from one worksheet to another worksheet.
Related Column: Related columns allow Worksheets to display data from other Entities. Related columns are highlighted in "Yellow" colour.
Below is an example of How to incorporate column from one worksheet to another:
------------------------------------------------------------------------------------------------------------------------------------------
First worksheet : Products
Products worksheet is assinged with two filter additionally to reduce the amount of products to the ones of a specific Brand.
Second Worksheet : Product Costs
As we can see from the below screenshot, there are huge records regarding product costs.
The product cost worksheet does not have the column "Brand" and "Product level 1 description" inorder to filter the Product costs worksheet specific to brand "Coca-Cola" .
It has other data such as "C&C List price" and "FSD List price" which will be helpful to understand the prices for required Brand.
In order to incorporate the columns from "Products" worksheet to "Product costs" we need to establish a relationship between the worksheets.
Select the worksheet where in you would like to bring the columns from other worksheet. In this case "Product Costs" and click on "Settings"
You will get moved to a screen like below. To create a new relationship between worksheets.
- Click on "Relationship"
- Click on "New"
- In the column "Related to" select the worksheet from which you would like to inherit the columns. In this case "Products"
Join worksheets by selecting the worksheet that should be joined, choose a join type and choose how to join the worksheets (join conditions: find the two fields that exists in both worksheets, so that they work as a reference to each other).
Join Types:
- Inner Join: Selects all records from Table A and Table B, where the join condition is met. All other rows are discarded.
- Left Join: Select all records from Table A, along with records from Table B for which the join condition is met (if at all). No rows from Table A are discarded.
- Right Join: Select all records from Table B, along with records from Table A for which the join condition is met (if at all). No rows from Table B are discarded.
- Full Join: Select all records from Table A and Table B, regardless of whether the join condition is met or not. No rows are discarded.
In this case : We will select "Left join" and provide the join conditions. We need to have atleast one column similar which exsist in both worksheet. As you can see below Source Column (Product ID) = Related Column (Product ID)
There is possibility to add mulitple conditions.
Afterwards click on “Go back to Workbook” button in the upper right corner.
Click on Manage Columns and choose “manage related columns” and then choose the columns "(+) " you would like to add to your currently chosen worksheet from the other worksheets you have joined.
In this case we would like to add the column "Brand" and "Product Level 1 Description"
The final results "Related Columns in Yellow"
