Formula for populating data summary tab (Dasboard)

[BenHornsby2021]

Good morning,

I am stuck with a formula that I am trying to use to complete a dashboard showing which projects our partnership organisations work on with us. I have attached a spreadsheet with two example tabs:

1. Master List

This is the worksheet our team uses to add partnership details. The last 5 columns are used to indicate (with a 'Y') which of our key projects each partner is involved with.

2. Dashboard

The table I have added here shows the names of the partners, pulled through from the Master List, but what I really need is a formula that will also bring over the projects that they are involved with from the Master List (Columns O to T). This will populate my Partners by Project table in the Dashboard. Is it worth using a formula, or would a Pasted Link be able to do this adequately?

[AliGW]

Is it worth using a formula, or would a Pasted Link be able to do this adequately?

No. As soon as you resort anything, your links will get out of synch.

As you have Office 365, you can update all of your formulae.

In G14 (but you MUST remove all data in those table columns first):

=FILTER(SORTBY(MasterList!$A$4:$A$325,MasterList!$A$4:$A$325,1),MasterList!$A$4:$A$325<>"")

In H1 (etc.):

=SUBSTITUTE(FILTER(SORTBY(MasterList!$O$4:$O$325,MasterList!$A$4:$A$325,1),MasterList!$A$4:$A$325<>""),"0","")

In A14:

=FILTER(SORTBY(MasterList!$A$4:$A$325,MasterList!$A$4:$A$325,1),MasterList!$C$4:$C$325=A$13)

In B14 (etc.):

=FILTER(SORTBY(MasterList!$A$4:$A$325,MasterList!$A$4:$A$325,1),MasterList!$C$4:$C$325=B$13)

[JohnTopley]

This ??

in H4

=VLOOKUP($G14,MasterList!$A$4:$T$500,COLUMNS($A:O),FALSE)

copy across and down

[AliGW]

@John

Not sure about that - some of the companies are on the list more than once. VLOOKUP will return only the first instance.

[Shareez Saleem]

Partners by Category
Copy & paste the across:

=IFERROR(SORT(UNIQUE(FILTER(MasterList!$A$4:$A$800,MasterList!$C$4:$C$800=Dashboard!B$12))),"")

Partnership links by project
=FILTER(MasterList!$A$4:$A$800,FILTER(MasterList!$A$4:$T$800,MasterList!$A$3:$T$3=Dashboard!H$12)="Y")

[BenHornsby2021]

Thanks to everyone for coming back so quickly!

AliGW - I have used the formula in G14 to list them and have had a thought that for the Dashboard, I don't need to have the multiple instances of names, whereas I do on the Master List.
Can the formula be adapted to handle just having one instance of each name in the list running from G14?


---ADDED POST SUBMISSION----

Actually - I have just realised that I would then need to look at the project columns for all in the Master List and have it translate into one row in the Dashboard - very complex!

[AliGW]

Try this:

=UNIQUE(FILTER(SORTBY(MasterList!$A$4:$A$325,MasterList!$A$4:$A$325,1),MasterList!$A$4:$A$325<>""))

[BenHornsby2021]

Hi Ali!
I have just amended by previous post as it raises complexities. Let's say 3 different entries on the Master List cover 2 or 3 projects, that needs to be read and then added to the newly truncated single row on the dashboard and still show each project correctly. I think that would be very complicated......but I don't really know!

[AliGW]

OK - let's start again.

Instead of shifting goalposts, please produce a FRESH sample workbook showing 10-15 rows of data (manually mocked up) of what you want to see.

This will save a lot of potential trial and error. SHOW us what you want.

[BenHornsby2021]

Hi Ali,
Thanks - and that's a really good idea! So, attached is a mock-up. You will see from the Master List, going as far down as 'Creative Youth Network' that where there are multiple entries for the name, it corresponds to different projects in Columns O to T. In the example of Creative Youth Network, there are 4 possible projects across the 3 entries (corresponds to 3 different contacts at their end).

On the Dashboard I have applied your UNIQUE(FILTER(SORTBY........... formula to show how I would like this to translate to single instances of the name, but in the case of an example like the Creative Youth Network, I would like all the project instances aggregated into one row for the one instance of the name. There is an example of this for Avon & Somerset Constabulary (first entry) as well. Does that make sense?

I am learning so much already by using the forum, so thank you. I do go away and practice with the formulae that I discover and although it is like reading the Matrix sometimes, I can see the world emerging. If I am to be The One, then you are surely the Oracle! Thanks for your help so far, it is much appreciated.

[AliGW]

In H14 copied down:

=IFERROR(LOOKUP(2,1/((MasterList!$A$4:$A$325=G14)*(MasterList!$O$4:$O$325="Y")),MasterList!$O$4:$O$325),"")

In I14 copied down:

=IFERROR(LOOKUP(2,1/((MasterList!$A$4:$A$325=G14)*(MasterList!$P$4:$OP$325="Y")),MasterList!$P$4:$P$325),"")

etc.

[BenHornsby2021]

That's perfect - thank you so much! That has solved my problem completely.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[josephteh]

May I offer an alternative solution: PivotTable. PivotTable is easy to use and require little maintenance.

But first convert your data into an Excel Table (Ctrl+T), so that any new data added will automatically be included in the PivotTable data source.

Then add PivotTable > Insert > PivotTable > OK > add "Organisation" to Rows section, and "Projects" to Values section.

Add Slicers to filter.