Formula to pull through names of all projects relating to a programme

Cheryl Collins

Hi

I'm looking for a formula that will reference a sheet that holds programme and project names. I want to pull through all projects related to a particular project. How can I best do this?

I did put it in a report but, on my Dashboard it means I can't pull the data through on my metric widget. I don't want it to show on my dashboard as a report as it won't look nice.

TIA

Genevieve P.

Hi @Cheryl Collins

You're close! In the COLLECT formula you need to first specify the column it's bringing content back from (Projects), then you need to specify the column that has criteria to match (programme), and finally the criteria it's looking for.

You're missing the second column, referencing the Programme column.

Try this:

=JOIN(COLLECT({Current and Pipeline Projects Range 2}, {Programme Column}, [Programme Name]#), " , ")

Ranges:

{Current and Pipeline Projects Range 2} - this should be looking at your Project Name column.

{Programme Column} - this should be looking at the helper Programme Name column.

Does that make sense?

Cheers,

Genevieve

Genevieve P.

Hi Cheryl,

To do this cross-sheet and have them appear in individual cells you would need to have a unique identifier per-row that you can search for and use as a criteria. You would then actually use an INDEX(MATCH formula to bring in this data, as well.

Is the Project ID in your first screen capture unique per-row? If you have this set up in your second sheet you can use this as the identifier to bring in Project Names, and also to bring in PMs in a different column.

Genevieve P.

Hi @Cheryl Collins

You can use a JOIN(COLLECT formula to Join together all the data from one column based on criteria in another.

Here's an example...the ranges in {these} are cross-sheet references to your first sheet. The values in [these] represent the column in sheet 2, where the formula is being written.

=JOIN(COLLECT({Sheet 1 value to return}, {Sheet 1 Range 1}, [Column 1]@row, {Sheet 1 Range 2}, [Column 2]@row))

So for your instance, something like this:

=JOIN(COLLECT({Project Name}, {Project ID}, [Project ID]@row), " / ")

At the end of the formula I've specified " / " as what I want to separate the values, but you can add in something else, such as a comma or even a line break using CHAR(10) and then tex-wrapping the cell.

You can read more about each of these functions in our Help Center: JOIN function / COLLECT function / Cross Sheet References

Let me know if this works for you!

Cheers,

Genevieve

Cheryl Collins

Screenshot1.png

Hi Genevieve

I've got the JOIN formula to work, which was helpful but, I can't seem to get the JOIN(COLLECT formula to work.

I've used my sheet from my original screen shot and the screen shot attached is the sheet I need the project names to pull onto based on the Programme Name field on my Sheet Summary.

Genevieve P.

Hi @Cheryl Collins

You're close! In the COLLECT formula you need to first specify the column it's bringing content back from (Projects), then you need to specify the column that has criteria to match (programme), and finally the criteria it's looking for.

You're missing the second column, referencing the Programme column.

Try this:

=JOIN(COLLECT({Current and Pipeline Projects Range 2}, {Programme Column}, [Programme Name]#), " , ")

Ranges:

{Current and Pipeline Projects Range 2} - this should be looking at your Project Name column.

{Programme Column} - this should be looking at the helper Programme Name column.

Does that make sense?

Cheers,

Genevieve

Cheryl Collins

Hi Genevieve

This worked great, thank you!

My next question a bit of a follow on would be, is now I have pulled those projects through into one cell, how would I split them into separate cells to show one project per cell?

The RIGHT and LEFT formulas won't work I don't think unless you know different?

Last question, I promise! 😃

Genevieve P.

Hi @Cheryl Collins

Breaking it out into different cells gets a bit complicated. You could potentially use LEFT and RIGHT but we would need an identifier to tell it where to pull from, and a number of characters for where to stop, which would likely be unique for each of your programmes.

What if instead you used CHAR(10) to put each Project on a new line in the same cell?

You'd use this instead of " / " to split up the values, like so:

=JOIN(COLLECT({Project Name}, {Project ID}, [Project ID]@row), CHAR(10))

Would that create the look you'd want?

Another option would be to use the Metric Widget to simply select the cells you want to bring in from the source sheet, manually.

Cheers,

Genevieve

Cheryl Collins

Hi @Genevieve P

I used the following formula:

=JOIN(COLLECT({Current & Pipeline Projects Range 2}, {Current & Pipeline Projects Range 5}, [Programme Name]#), CHAR(10))

But it is has just pulled through the results in a long string in the same cell (see screen shot below). Have a I missed something?

Many thanks

Cheryl

Genevieve P.

Hi Cheryl,

My apologies, I should have specified. You'll need to use the wrap-text function on the cell to split it up onto different lines:

Cheryl Collins

Hi Genevieve

Aah, yes I see what you mean. I ideally need them in separate cells so I can then do an INDEX MATCH formula in the sheet summary to look up PMs responsible for the individual projects.

Ideally this is what I need to get to:

Thanks

Cheryl

Genevieve P.

Hi Cheryl,

To do this cross-sheet and have them appear in individual cells you would need to have a unique identifier per-row that you can search for and use as a criteria. You would then actually use an INDEX(MATCH formula to bring in this data, as well.

Is the Project ID in your first screen capture unique per-row? If you have this set up in your second sheet you can use this as the identifier to bring in Project Names, and also to bring in PMs in a different column.

Cheryl Collins

Hi Genevieve

I think for the amount of benefit it's going to bring vs. effort, I will just copy and paste the projects related into the roll up sheet. Maybe a product enhancement suggestion would be to have a SPLIT formula function that works like it does in Excel.

Thanks so much for all your help though, you have given me ideas for other things I can improve :-)

Cheryl

Genevieve P.

No problem! Other Community members may have different suggestions. Please feel free to post again if you come across something else!