Create References in Derived Column Cells

Since R2024b

If you want to access cell values from Safety Analysis Manager spreadsheets and use them in derived column cells, you can create references. You create references from cells in derived columns to other kinds of cells in one or more spreadsheets. For more information on derived columns, see Define Derived Values. After creating the references, you access the data in each cell that contains references by using the in the derived column formula. You can interactively or programmatically create and manage references.

Create Spreadsheet Cell References

To create references:

1. Open the Safety Analysis Manager. In the Apps tab, in MATLAB^® or Simulink^®, click Safety Analysis Manager.

2. Open at least one spreadsheet that contains a derived column, or create a new spreadsheet that contains a derived column and save it.

3. Select at least one cell that you want to reference. In the Spreadsheet section, click Add Reference > Select for Referencing.

4. Select at least one cell in a derived column that you want to contain the references. In the Spreadsheet section, click Add Reference > Create Reference to Selection.

After creating the references, you can view the references assigned to each derived column cell by clicking on the cell. In the Properties pane, in the Cell pane, expand the References section to view the references.

Each reference displays the row and column label for each referenced cell. Click the reference to highlight the referenced cell in the spreadsheet.

You can create references to cells in different spreadsheets, or to cells in the same spreadsheet. Each reference displays the spreadsheet that contains the referenced cell.

Each spreadsheet that contains a referenced cell must be on the file path.

Referenced cells are invariant. For example, if you add rows or columns to the spreadsheet, the referenced continues to refer to the same cell, even though the indexes are different. To move a reference to a new cell, delete the reference and create a new one.

Access Referenced Cell Values in Derived Column Cells

After you create the reference, you can retrieve the referenced cell values in the derived column formula by using the sfa_referencedValues keyword. For each cell in the derived column, the keyword returns the referenced cell values as a cell array in the order listed in the References section. You can reorganize the references in the pane by dragging and dropping each reference. Consequentially, if each cell in the derived column has different reference, the keyword changes value. If the cell does not have references, the keyword returns an empty value.

For example, this column formula assigns each derived column cell value to an ordered list of their referenced cell values.

for index = 1:numel(sfa_referencedValues)
    sfa_derivedValue = sfa_derivedValue + newline + "(" + ...
        num2str(index) + ") " + string(sfa_referencedValues{index});
end

Programmatically Configure References

You can also configure references programmatically.

• Add references to a cell by using the addReference function on the SpreadsheetCell object of that cell.

• Get the cell values of the referenced cells by using the getReferencedValues function. To return the properties of the referenced cells, use the getReferences function. The getReferences function returns the referenced cells as a cell array of CellReference objects.

• Reorder references by using the moveReference function.

To delete an existing reference, use the removeReference function.

Reference Spreadsheet Example

This example shows a spreadsheet that performs a calculation by referencing cells in a different spreadsheet. The spreadsheets are a modified version of the spreadsheet in the Spreadsheet Callback Example. The spreadsheets represent a Failure Mode and Effects Analysis (FMEA) that assesses possible failures in the volume control of a mixing vessel. The FMEA assesses the importance of the failure by calculating the risk priority number (RPN) for each failure mode, its cause, and its likelihood of detection. In this example, the analysis stores the to column that calculates the risk priority number (RPN) into one spreadsheet, and the values used to calculate the RPN in another spreadsheet.

Inspect the Spreadsheets

Open the rpnValues.mldatx and rpnEvaluation.mldatx files to view the spreadsheets.

safetyAnalysisMgr.openManager
safetyAnalysisMgr.openDocument("rpnValues.mldatx");
safetyAnalysisMgr.openDocument("rpnEvaluation.mldatx");

The rpnEvaluation spreadsheet evaluates the RPN by referencing cells contained in the rpnValues spreadsheet. To view the references in the rpnEvaluation spreadsheet, select a cell in the derived column named RPN and, in the Properties pane, expand the References section.

Inspect the RPN Calculation

To see how the RPN column calculates the RPN from the values in the rpnValues spreadsheet, view the column formula. In the rpnEvaluation spreadsheet, right-click the derived column label and click Edit Formula. The formula retrieves the values of the referenced cell values by using the sfa_referencedValues keyword. The keyword returns the values as a cell array, and the formula converts the cell array into a numeric array. The formula then checks if each cell in the derived column formula contains at least three referenced values and that each referenced cell value is a number. If those conditions are true, it assigns the product of the referenced cell values to the derived column cell value.

rpnValues = str2double(sfa_referencedValues);

if anynan(rpnValues)
    sfa_derivedValue = "Unable to calculate";
elseif numel(rpnValues) < 3
    sfa_derivedValue = "Unable to calculate";
else
    sfa_derivedValue = prod(rpnValues(:));
end

In this example, the fifth and sixth rows in the rpnValues spreadsheet do not contain enough values for the RPN column to calculate values in the fifth and sixth cells. Adding numeric values to the empty cells in the Severity, Failure Probability, and Detection Rating columns automatically updates the RPN column cells.