Intelligent Excel formula needed!

[Macindows]

I OFFER €10 (PAYPAL) TO THE ONE WHO SOLVES THIS PROBLEM AND EXPLAINS IT TO ME IN DETAIL. NO JOKE!

Situation is the follwing:
I have a large spreadsheet with about 600 entries and 6 different colums.
This is supposed to be the "source" for the following sheets.
The 600 entries in the large spreadsheet are subdivided into different funds. I want Excel to summarize certain funds and link those to a new sheet but not only the funds but also the entries to the left and right hand side.
In detail:
Let's say there is "Fund 1 XXX 111" which has 7 accounts with all different information. Then there is "Fund 1 XXX 222", similar to the first one, with 8 accounts. Excel should now recognize this fund to be the same Fund as the first one and copy and link both of the funds INCLUDING all entries beside the "fund name" to a new sheet.
So whenever I copy a completly new "source" spreadsheet with 600, 700 or 800 entries, Excel should recognize the different funds, combine the most important ones and update the new information on the already existing sheets.

I'd appreciate it very much if any of you guys knew this.
Thanks a lot!

[starguy]

Please provide a sample workbooks in zip format.

[Macindows]

Okay, I attached a working sample. Please rename it to *.xls.
Couldn't zip it since I haven't got a zip program installed on this computer, even not the Windows version.

Thanks!

[Macindows]

I OFFER €10 TO THE ONE WHO SOLVES THIS PROBLEM AND EXPLAINES IT TO ME IN DETAIL! NO JOKE!

[Brigitte_P]

Do you already know exactly what funds and accounts you want summarized? Will there ever be new ones? Will there often be new ones?

If the funds and accounts you want summarized are always the same ones, you can use the sumif function to summarize. You just need to build your summary sheet and use that function for each account of each fund.

The SUMIF function allows you to look at a table of data and sum the values in one column when the value in another column match a certain condition. Try looking it up in the help file, it's quite useful.

If your funds and accounts can change, you're probably going to have to build a procedure in VBA to do what you want, and your summary will not update quite automatically: you'll have to run the procedure to refresh it, although you can set it up so the procedure runs automatically each time you open or save the workbook.

So, the answer depends on how much flexibility you need.

Brigitte

[Macindows]

Brigitte, thanks a lot. Are you as well German?
To my fund list:
Entries will change from time to time. Sometimes only the comitted amount changes, a client invests into the fund. Sometimes, a new fund will be created and new clients will invest in these new funds. But basically, the main structure will consist. So in my case: Fundxxx will consist but new funds will be named to e.g. Fundxxx 123 and the next time Fundxxx 456.
Then it could also be, that in one month from now, there are three new clients investing in several specific funds, which should obviously be included in the fund analysis, which lists all clients who invest in the fund.

I'm reliefed that finally found a solution for my problem. If you insist, I will give you the reward! ;-)

[Brigitte_P]

I'm a French Canadian.

If you don't mind rebuilding your summary sheet to add new funds when needed, SUMIF should work fine.

Glad I could help, and don't worry about the reward.

Brigitte