VBA Summarize data based on information in table

[talt0612]

Hi all=
I wonder if anyone has a slick solution for this problem. I have information in a table that corresponds to data in an excel file. I need help with some code that will use the information listed in the table to summarize the data in the excel file and drop the output in a new sheet. I need the table to be dynamic so that other users can enter new information to the table and the output will reflect the changes. Here is an example:

InputTable:

[TABLE="class: outer_border, width: 500, align: center"]
<tbody>[TR]
[TD]CodeISIN[/TD]
[TD]ccy[/TD]
[TD]DataColumn[/TD]
[TD]LoadDate[/TD]
[TD]FundName[/TD]
[TD]OutputType[/TD]
[/TR]
[TR]
[TD][TABLE="width: 211"]
<tbody>[TR]
[TD="class: xl65, width: 211, bgcolor: #DBE5F1"]LU0367993408[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]EUR[/TD]
[TD][TABLE="width: 195"]
<tbody>[TR]
[TD="class: xl67, width: 195, bgcolor: #DBE5F1"]Net Amounts
(Class Currency)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3/1/13[/TD]
[TD]S-53768-EUR[/TD]
[TD]Subscription/Redemption[/TD]
[/TR]
[TR]
[TD][TABLE="width: 211"]
<tbody>[TR]
[TD="class: xl67, width: 211, bgcolor: #B8CCE4"]LU0432679735[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]EUR[/TD]
[TD][TABLE="width: 195"]
<tbody>[TR]
[TD="class: xl67, width: 195, bgcolor: #DBE5F1"]Net Amounts
(Class Currency)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 149"]
<tbody>[TR]
[TD="class: xl67, width: 149, bgcolor: #DBE5F1"]3/1/13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]S-53768-EUR[/TD]
[TD]Subscription/Redemption[/TD]
[/TR]
[TR]
[TD][TABLE="width: 211"]
<tbody>[TR]
[TD="class: xl67, width: 211, bgcolor: #DBE5F1"]LU0641126270[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]GBP[/TD]
[TD][TABLE="width: 195"]
<tbody>[TR]
[TD="class: xl67, width: 195, bgcolor: #DBE5F1"]Net Amounts
(Class Currency)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 149"]
<tbody>[TR]
[TD="class: xl67, width: 149, bgcolor: #DBE5F1"]3/1/13[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]S-56697-GBP[/TD]
[TD]Subscription/Redemption[/TD]
[/TR]
[TR]
[TD][TABLE="width: 211"]
<tbody>[TR]
[TD="class: xl67, width: 211, bgcolor: #B8CCE4"]LU0367993747[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]SGD[/TD]
[TD][TABLE="width: 195"]
<tbody>[TR]
[TD="class: xl67, width: 195, bgcolor: #DBE5F1"]Net Amounts
(Class Currency)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]3/1/13[/TD]
[TD]S-73875-SGD[/TD]
[TD]Subscription/Redemption[/TD]
[/TR]
</tbody>[/TABLE]

The idea is to open the data source, and filter by "CodeISIN" and "ccy" and then pull the value from the "dataColumn" column. Then the info needs to be aggregated in to a new sheet. In the new sheet (OutputSheet), the information will be sorted by "FundName" and "LoadDate" will be the current date and "OutputType" will be "subscription/redemption". For instance in the OutputSheet, the top two rows in the above table would be combined (i.e. the values from the "DataColumn" data source sheet would be combined) because they both correspond to the "FundName" S-53768-EUR.

Then, Ideally a user could add another row to the bottom of the existing table and that information would also be captured.

Normally I'd use a pivot table for something like this, but there are a number of different users accessing the sheet and I don't want them making changes to the pivot without my knowledge.

I'm forever in debt to anyone who has a slick solution to this. Thanks in advance.