Input formulas in a defined data range and convert results as valu
 

Hi,

I have a dataset for users to view data. It has the following format till
year 2010 and have portions of topics like forecast, shipment, aging .... and
the whole dataset can go very long vertically. Within each topics is the type
of products measured, below is an extracts of my dataset:

Forecast Accuracy Jan 2007 Feb 2007 Mar 2007
Apr 2007

Prod A x x
x x
Prod B x x
x x
Prod C x x
x x
Prod D x x
x x

Actual Shipment Jan 2007 Feb 2007 Mar 2007
Apr 2007
Prod A x x
x x
Prod B x x
x x
Prod C x x
x x
Prod D x x
x x

I will want to automatically input in the SUMPRODUCT formulas into those 'x'
cells above to pull data from another worksheet (3) for example. I have a
login macro page in another worksheet which has a drop down list to ask users
to select their country to login and click on my macro. My macro will then
point to this particular country's dataset worksheet and hide the rest. One
dataset is for each country in one sheet. My intention is to input use
SUMPRODUCT formula to extract out all the relevant data for all the portions
in the dataset from the raw data in worksheet (3) based on the country code
login by users. The raw data on worksheet (3) has all data for all the
countries.

How do i auto feed in all the formula in those 'x' cells, query and pull
the needed data from worksheet (3) for the current month say Jan 2007 and
paste it as value after data is pull over. Need to over right the formula as
value as i will empty the raw data in worksheet (3) moving into next month
Feb.

Thanks