Introduction
One can use Source Data component to push data into other cell by changing the index of the component. One can copy values from one location (source) on the spread sheet to another (destination).
The best part is that it won't be displayed during runtime.
Below image shows the location of the component:
Scenario
One may use this component in situations where it requires to display cell contain based on user's selection or with indirect selection (in case of what - if scenarios).
There might be situations where one needs to copy an entire row/column depending upon selection. Selection can be based value selected on selectors, tabs etc.
One may think that the same functionality can be achieved with some Excel functions such as HLOOKUP(), VLOOKUP(), Nested IF() function etc. Functions like HLOOKUP() and nested IF() statements are more expensive at runtime than this standard component. So it is wise to use this component in places where we can avoid the use of heavy functions.
Property Screen
The property panel of this component is quite simple. It is consists on=f only two tabs:
- General
- Behavior
General tab contain Insertion Type, it is same as in other components, it is consists of
- Columns : when one want to copy entire column, similar to VLOOKUP() function.
- Rows : when one want to copy the entire row, similar to HLOOKUP() function.
- Value : when one want to push the value of a single cell in the source field to another cell (destination).
Snapshot is as below:
Then comes the Source Data it's the location (array of data), from where one want to push data from.
Destination can be row/column or a single cell in accordance with the Insertion Type.
Note:- Make sure that the source and destination are ni sync, means in case of Insertion type "column" the destination must be made up of same number of rows.
Next one is the Behavior tab:
Here Selected Item Index is the column/row of or the cell no that is intended to be copied. This value can be passed with the help of standard selectors (a Label Based Menu component is used)
Note: The Index starts from 0 (numeric value). That means 0 stands for first selection.
Steps:
This example shows a simple way to copy columns, according to the label selection from user.
We took a Label component that is used to select the index value for the Source data component, which in turn push the data of the corresponding column to destination.
One Pie Chart to display Division wise breakup of sales and a Source Data component. The destination is mapped with the Pie Chart.
Note: We kept the Source Data Button on the top of all components. As it won't be displayed during runtime, we need not to worry about its location on the design canvas.
Canvas will look like :
Mapping:
Source Data:
- Insertion Type : Column
- Source Data : Sheet1!$B$4:$E$9
- Destination : Sheet1!$C$14:$C$19
- Select Item Index : Sheet1!$G$5
Pie Chart :
- Subtitle : Sheet1!$C$14
- Values : Sheet1!$C$15:$C$19
- Data in Columns
- Labels : Sheet1!$B$15:$B$19
Label Based Menu
- Labels : Sheet1!$B$4:$E$4
- Insertion Type : Sheet1!$B$3:$E$3
- Destination : Sheet1!$G$5
- Behavior : Selected Item : Label1
The embedded Excel will look like :
Now whenever we select a different Label a new set of data will populate in the destination cells. Cell B3 to E3 is taken as index selection cells (where o represents the 1st column).
On preview it will look like as below:
3 Comments
