Tables with changing data.

[dnicholsby]

Hello all,

First post and I hope it makes sense.

Basically, I work as an analyst for a wealth management company. I am in the process of creating a spreadsheet which provides our employees with a centralised database to extract fund and portfolio performance data.

We use approximately 150 funds which are members of 33 different sectors. In order to compare performance we usually compare the fund performance to the sector performance. Users select the funds that are in a client's portfolio in the currnet spreadsheet and it provides them with a table of performance for those particular funds. It also provides the sector the fund is a member of and the performance of that fund.

My issue is that I want the table to look like this:

Performance
Sector A %
Fund A1 %
Fund A2 %
Sector B %
Fund B1 %
Fund B2 %
Fund B3 %
Sector C1 %
Fund C1 %

So essentially it groups the funds by sector. In mose cases not all sectors will be part of the table.

I have seperate sheets which show which funds belong to which sector and the performance of all sectors and funds.

How can I create a table that will only the show the sectors of funds that have been selected AND group the funds under each sector - so if there are more than 2 funds in the same sector it only shows that sector's performance once?

Can i do it with formulas? Or do i need macros? Or possibly with pivot tables? I don't have much experience with pivot tables but any suggestions would be greatly appreciated as I plan to work on this tomorrow!

[TMS]

Don't use separate sheets. Put everything on one sheet. Column A, Sector ... use Data Validation to select. Column B, Fund. Column C onwards, performance data.

Make the data into a Structured Table. Then you can sort it and filter it. And use Pivot Tables to analyse and summarise the data.

[dnicholsby]

Thanks for the reply however that assumes the user knows which fund belongs to which sector which is not the case.

The user selects the funds using data validation and I want everything else to be automated so they can just paste the table into their report (in word)

PS. Sorry if my reply seems ignorant - I have also never worked with structured tables!

[dnicholsby]

hi again, judging by the lack of replies I thought i'd try and bump this with more data.

Basically my data looks like the attached.

The data under investment funds may change, depending on which funds the user picks. This therefore changes the Sector and the figures.

I want the final output table to look as I've explained.

Is this possible?