Formula

[Guest]

How can I do this.
From a table below
A B C D E
1st 2nd 3rd 4th

1 Sales 24 22 29 26
2 Bouns 6 5.5 6.8 6.1
3 Commission 3 2.5 3.5 4.9


On my next worksheet I have 3 fields

1st is Sales
2nd is Quarter
3rd is Amount

When I select 1st two filed which is Sales and Quarter, I need the amount to automatically show up. What formula do I need.

Thnx
Prakash.....

 

[JulieD]

Hi Prakash

this is similar to your other worksheet (which i've sent you back) the
offset & match combination

the formula for amount is
=OFFSET(Sheet1!$A$1,1,MATCH(Sheet2!B1,Sheet1!$B$1:$E$1,0))
where Sheet1 holds the table as per your example
and Sheet2 holds the other fields (B1 = Quater value)
and where sales = the Amount value
if however Amount = Sales + Bonus + Commission you will need
=(OFFSET($A$1,1,MATCH(B8,B1:E1,0)))+(OFFSET($A$1,2,MATCH(B8,B1:E1,0)))+(OFFS
ET($A$1,3,MATCH(B8,B1:E1,0)))
-- there might be a neater way of doing this but this is the only one i can
get working.

Regards
JulieD

How can I do this.
From a table below
A B C D E
1st 2nd 3rd 4th

1 Sales 24 22 29 26
2 Bouns 6 5.5 6.8 6.1
3 Commission 3 2.5 3.5 4.9


On my next worksheet I have 3 fields

1st is Sales
2nd is Quarter
3rd is Amount

When I select 1st two filed which is Sales and Quarter, I need the amount

to automatically show up. What formula do I need.