Refer Hidden Sheet Data

CELF can refer cell information in another sheet which is not operated, and call action set in another sheet.

Here is an example to refer another sheet while creating a sample application.

In sample application, prepare 2 sheets, 'Display result sheet' and 'Hidden summary sheet', and then get and summarise data in 'Hidden summary sheet', and lastly display the summary result in 'Display result sheet'.

Create 'Hidden summary sheet'

1. Create 'Hidden summary sheet'.

[1] Dealer Code

Set dealer code to be search condition.

[2] Total summary

It is to display total summary in 'Summary' column by SUM function.

[3] Sales data

It displays data in 'dummy_sales_data' table.

[4] Summary

It is to display the value of 'Unit price' column * 'Quantity' column.

2. Create action set 'get sales data' to get data from 'dummy_sales_data' table.

Here is to get sales data identified with set 'Dealer code', and display the data of 'Item name', 'Unit price', 'Quantity'.

3. Set created action set into 'Update' button.

Tip

After clicked 'Update' button, data is displayed like this.

Create 'Display result sheet'.

1. Create 'Display result sheet'.

[1] Dealer Code

Set dealer code to be search condition.

[2] Sales total summary

It is to display total summary on 'Hidden summary sheet'.

2. Create new action set, and then 'Action set setting' dialog is opened.
3. In order to copy the value of 'Dealer code' cell from 'Display result sheet' to 'Hidden summary sheet', set 'Set value in sheet cell' action.

[1] Sheet

Set sheet with value destination setting cell.

[2] Destination setting cell

Set destination setting cell. Set cell in 'Hidden summary sheet' which is value desitination setting sheet.

[3] Original source cell

Set original source of value. Set cell in current active 'Display result sheet'.

Tip

In 'Set value in sheet cell' action, it is possible to set value into cell in another sheet.

4. In order to execute action set 'Sales data' in 'Hidden summary sheet', set action like this.

[1] Sheet

Set sheet which has called action set.

[2] Action set

Set action set to call.

Tip

In 'In sheet run action' action, you can run the action set to be defined in another sheet.

5. To copy from 'Total summary' cell in 'Hidden summary sheet' to 'Total sales summary' in 'Display result sheet' cell, set the following action set.

[1] Destination setting cell

Set destination setting cell of value. Set cell(Total sales summary) in current active 'Display result sheet'.

[2] Original source cell

Set original source of value. Set cell (Total summary) in current active 'Hidden summary sheet'.

Tip

You can get cell value in another sheet by setting 'sheet name!cell number' format.

6. Set created action set into 'Update' button.

Refer Data In Another Sheet

1. Input 'Dealer code' in 'Display result sheet'.

2. Click 'Update' button.

Total summary of sales data in 'dummy_sales_data' table was displayed in 'Total summary'.