Return Multiple Rows

Andrea Westrich

I am trying to reference a centralized Goal Document containing the 2021 goals for all our departments. From our Org Sheet, I'd like to reference the Goal Document and return the goals by matching the department name in both sheets. I need it to return multiple rows of goals for each department.

The idea is to have a living Goal document that will update the Org Sheet as goals change.

See my attached Goal and Org photos for reference.

Thank you

Mark Cronk

Hi Andrea,

If the goals can be in the same cell you can use JOIN(COLLECT())

In your org sheet in the goals column add:

=JOIN(COLLECT({objective:objective}, {department:department}, @cell= department@row), char(10))

Replace my { } with named ranges back to the columns on your Goal Document. On your org sheet I'm assuming you have a Department column.

Work?

Mark

Andrea Westrich

@Mark Cronk I'm getting #unparsable. This is my formula:

=JOIN(COLLECT({Goals}, {Department}, @cell = Department:Department@row, CHAR(10))

Where Goals is the column of goals in the other sheet and Department is the column of departments in the other sheet. Department:Department is the Department column in the sheet where the formula is for matching. It does highlight the proper cell where I'm starting from if that helps.

To be clear, I don't want all the goals in one cell, I want to return all rows for each department as rows in the Org sheet. Does that make sense?

What does char(10) do?

Mark Cronk

Hi Andrea,

This formula should work but it doesn't do what you asked for. Char(10) inserts a hard return after each value. You'll get a list of goals in the formula cell.

=JOIN(COLLECT({Goals}, {Department}, @cell = Department@row, CHAR(10))

To return all rows for each department as rows in the Org sheet I think you can use a report. Create a new report linked to both your goal and org sheet. Select the columns you want to see and the filtered you want applied. Group by department. Close?

Mark