Skip to Content
Author's profile photo Debjit Singha

Part 4 – Using Source Data Component


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:




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.



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 :


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:

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Halomoan Zhou
      Halomoan Zhou
      I see the same effect when using the Label with Insertion Type: Column. What's the other benefit to use the Source Data component?
      Author's profile photo Debjit Singha
      Debjit Singha
      Blog Post Author
      Suppose in a situation where one don't want to use any select component on display (as we used a Label Based Menu component). Selection is to be done on some other basis and we also want to avoid expensive run-time functions like HLOOKUP().

      Another situation i can thought of is like suppose, on the selection particular section on the Pie Chart (displaying Sales company code wise) we want to drill-down and want to show other KPI's. In situation like these we can use source Data component instead of LOOKUP() function.
      Hope I clarified it correctly.

      Author's profile photo Former Member
      Former Member
      That would be a far more useful component if it supported the 'Filtered Rows' insertion type, rather than the more simple row/column insertion.

      Add it to the wish list....

      Good blog anyway - thanks!