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

T

Tan

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top