User defined function

[Kaizer]

In sheet "Stock" I have a little DBase that has names e.g. Regions, Countries, Accounts and Amounts by months (Jan to Dec) and by quarter (Q1 to Q4). On the "Report" sheet I have a drop-down list with periods (e.g. Jan, Feb,...Q1,...Q4) and countries/accounts. I need to create a function that would calculate an Array formula Depending on selected period. This function should take Period, County and Account as its arguments.

I was trying to make an If... formula but it gets very long. I thought function should make the job better. Can you help with the code for this function?

I attach a little file to my post for you to be able to see how the the report and DBase are structured.

Thank you in advance.

[matthewspatrick]

Kaizer,

You do not need a UDF--you need to redo Stock so that it really is a database, and not a crosstab.

Please see the attached workbook. I recast the Stock worksheet (Revised Stock), and then used a PivotTable to generate the report output.

[Kaizer]

Patrick, the thing is that I will have much more Regions/Countries/Accounts. It will be a very long list. Probably longer than 65k rows. That's why I made this DBase structure. Thought that the function can be a solution. Do you think it's possible to make one?

[matthewspatrick]

Kaizer, if you are going to have that much data, then ultimately your best bet is to punt Excel altogether and go to a real database platform.