Converting from Select or Multiselect Data Types to Related Rows

  • Updated

Using the Select and Multiselect data type allows you to limit or autofill the values your users can put into a cell. Sometimes though, you may wish to fill the select options with values from another worksheet. Or, perhaps, you want users to select from options that will link to additional information. For these situations, you need the Related row data type. Spreadsheet.com also allows you to easily convert existing Select and Multiselect cells or columns to corresponding values in linked worksheets.

How to convert from the select or multiselect data type to the related row data type

It is easy to convert from one data type to another data type. However, data conversions can cause data loss. It is best to backup data before converting (for instance, copying your workbook) or (if there is data loss) to undo.

When converting data from the select data type or the multiselect data type to the related row data type, ensure that the related values match your select or multiselect options. Typically values match if they are the same, for instance: "Underseas" and "Underseas", not "Under seas", "Under-seas" or "7c8e87".

Also, for Spreadsheet.com to automatically match values, the options in your select or multiselect lists must match values in the primary column of the linked worksheet.

To convert a column, cell, or range of cells from select or multiselect to related row, select the cell, cells, or column you wish to convert, right-click, and then select Edit data type. From here, an Update Column or Edit Data Type menu will appear. In the data type drop-down, select Related Row mceclip1.png and choose the worksheet to link to. You can link to worksheets in the current workbook or another workbook that you have sufficient permissions to access. 

After you click Update to confirm the conversion, Spreadsheet.com will convert the data, looking for matching values between your select list options and the values in the primary column of your linked worksheet.

In the animation below, we have a worksheet for a painting company tracking some upcoming projects. Previously the paint color field was a select list, but we change this to relate to the "Recommended Paint Colors" worksheet. We look to see that the paint names are listed under the primary column in our target worksheet. With that confirmed, we go back, select the Paint Color column, right-click, select Edit data type, change the column to Related row, and pick our worksheet to relate to. After updating, Spreadsheet.com matches values and converts them to related row links. We can even expand a linked row to see all entries in the related row.

Select_to_related_row_conversion.gif

After you have converted your select or multiselect cells or column to Related rows links, you can expand your related rows. Expanding a row shows a form view of all entries for the linked row. Also, after rows have been related, you can use the related row lookup data type to fill cells with values from their linked row. For instance, in the animation above, we could look up the paint cost and include that in our Painting Projects worksheet. 

For more on related rows, check out our Related Row Data Type article or all our articles on related rows.