Looking to return multiple row values from another sheet based on dropdown selection

Nate H

Greetings, Community,

I'm wondering if there is a way to return data sets of multiple rows based upon a drop-down selection from a form. What I'm looking to get to is I have a form for people to request testing on programs they implement. In another sheet (I'll refer to as the KPI sheet), I have the programs (primary column) along with each metric to be measured and their goals (subsequent and separate columns). Each metric is its own row in the sheet - so the program name repeats in the KPI sheet as there are multiple metrics with each program. I'm looking to find a way to pull all the metrics into the ticket sheet when a particular program is selected from the dropdown so they don't have to be manually transferred. If there were only one metric or one row to be pulled the VLOOKUP would be straightforward enough for me, but I haven't a clue how to address the multiple row issue.

Thanks in advance for all the help. This forum has been invaluable.

Genevieve P.

Hi @Nate H

There currently isn't a way to automate multiple row creations underneath a parent row or new row added from a Form. What I would do in this instance is set up an alert, then you can either manually copy/paste your KPIs over or use Copy Row to copy over the rows from your second sheet (see: Copy and paste hierarchy)

Please submit your feature request to the Product team by creating an Idea Post in the Smartsheet Product Feedback and Ideas topic here in the Community. This will allow other users to vote on your enhancement idea!

An alternative would be to bring all the KPI data from multiple matching rows into one cell in the intake form sheet. To do this you would use a JOIN(COLLECT instead of a VLOOKUP. As long as each row in your KPI sheet identifies that this KPI is associated with your dropdown value, it can bring in each of the KPI titles into one cell (for example, a multi-select cell).

The structure for this formula would look something like this:

=JOIN(COLLECT({KPI Details}, {Program Name}, [Program Dropdown]@row), CHAR(10))

The CHAR(10) is what would separate your values into multi-select options in one cell.

Cheers,

Genevieve

Genevieve P.

Hi @Nate H

There currently isn't a way to automate multiple row creations underneath a parent row or new row added from a Form. What I would do in this instance is set up an alert, then you can either manually copy/paste your KPIs over or use Copy Row to copy over the rows from your second sheet (see: Copy and paste hierarchy)

Please submit your feature request to the Product team by creating an Idea Post in the Smartsheet Product Feedback and Ideas topic here in the Community. This will allow other users to vote on your enhancement idea!

An alternative would be to bring all the KPI data from multiple matching rows into one cell in the intake form sheet. To do this you would use a JOIN(COLLECT instead of a VLOOKUP. As long as each row in your KPI sheet identifies that this KPI is associated with your dropdown value, it can bring in each of the KPI titles into one cell (for example, a multi-select cell).

The structure for this formula would look something like this:

=JOIN(COLLECT({KPI Details}, {Program Name}, [Program Dropdown]@row), CHAR(10))

The CHAR(10) is what would separate your values into multi-select options in one cell.

Cheers,

Genevieve