Lookup?

[Guest]

How to set the formula to return the value if I need to lookup the 'month', 'category' and 'region'?

 

[Bob Phillips]

Bit more detail?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

How to set the formula to return the value if I need to lookup the

'month', 'category' and 'region'?

 

[Guest]

Hi,

In a workbook, I have one worksheet to input the current month result of each region for each category:

Jan-04 Employment cost Milan 100
Jan-04 Rent Milan 20
Jan-04 Income Milan 50
Jan-04 Income Paris 200
Jan-04 Employment cost Paris 30
....... more items....

Then in another worksheet, I have to prepare an analysis (current month) for each region:
Jan-04 Milan Paris
Income 50 200
Employment cost -100 -30
Rent -20
Subtotal -70 170

Then in another worksheet, I have to prepare an analysis (YTD) for each region:
YTD Jan-04 Milan Paris
Income 50 200
Employment cost -100 -30
Rent -20
Subtotal -70 170

Then in another worksheet, I have to prepare an analysis for the region for each month:
Milan Jan-04 Feb-04 YTD
Income 50
Employment cost -100
Rent -70

So what the effective fomula than I can use so that i can just input in one worksheet and then automatically updated / linked to other worksheet?

Thanks and regards

 

[Frank Kabel]

Hi Janmy
though this can be done by formulas, looking at your worksheet layout
you should have a look at pivot tables to analyze your data (They are
just IMHO what you're looking for). To get started have a look at
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.cpearson.com/excel/pivots.htm

 

[Guest]

Hi Frank,
Yes, I know that I may use Pivot table. However, the format of the table is not the desirable one. So can I preset the format of the table?
Or using formulae are still easier?

Thanks and regards.

 

[Frank Kabel]

Hi
you can change the pivot table format (though this is one of the
negative points of pivot tables). IMHO formulas won't be easier

 

[Bob Phillips]

Jammy,

I will send you an example workbook if you send me you email address.

Send to (bob . phillips @ tiscali . co . uk - remove the spaces)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hi Frank,
Yes, I know that I may use Pivot table. However, the format of the table

is not the desirable one. So can I preset the format of the table?

 

[Guest]

Thanks Bob.
I have sent you email.

 

[Bob Phillips]

Solution in mail.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)