In a woorkbook I have two sheets

In one sheet I got a tabel With exchange rates one coulom per month and one
row per currency
January February
USD XXX XXX
Euro XXX XXX

In the other sheet I am standing i cell D4 where I like to get the correct
exhange rate. I got one cell (B4) with the months name an an other (C4) with
the name of the currency.

Do any have an idear ?
--
Palle korsholm

Hi,

In this instance the table of exc rates and months is laid out like this
Col A Col B Col C Col D Col E Etc
jan feb mar apr etc
usd 1 2 1.5 1.2
euro 3 4 1.6 1.7
etc

and the formula in D4 is
=SUMPRODUCT((Sheet3!B1:M1=B4)*(Sheet3!A2:A23=C4)*( Sheet3!B2:M23))

Mike

"Palle korsholm" wrote:

In a woorkbook I have two sheets

In one sheet I got a tabel With exchange rates one coulom per month and one
row per currency
January February
USD XXX XXX
Euro XXX XXX

In the other sheet I am standing i cell D4 where I like to get the correct
exhange rate. I got one cell (B4) with the months name an an other (C4) with
the name of the currency.

Do any have an idear ?
--
Palle korsholm

I have lookd at it but I might not have been clear about what i ment.

If I wright February in cell B4 and USD in cell C4 i like the exchangerate
to gent in tho d4. If i change the names in B4 and C4 I will get the new
excahgnerate

--
Palle korsholm


"Mike H" wrote:

Hi,

In this instance the table of exc rates and months is laid out like this
Col A Col B Col C Col D Col E Etc
jan feb mar apr etc
usd 1 2 1.5 1.2
euro 3 4 1.6 1.7
etc

and the formula in D4 is
=SUMPRODUCT((Sheet3!B1:M1=B4)*(Sheet3!A2:A23=C4)*( Sheet3!B2:M23))

Mike

"Palle korsholm" wrote:

In a woorkbook I have two sheets

In one sheet I got a tabel With exchange rates one coulom per month and one
row per currency
January February
USD XXX XXX
Euro XXX XXX

In the other sheet I am standing i cell D4 where I like to get the correct
exhange rate. I got one cell (B4) with the months name an an other (C4) with
the name of the currency.

Do any have an idear ?
--
Palle korsholm

Which is exactly what the formula does.
In sheet 3 cell B1 to M1 are the months Jan- December
In column A starting in A2 are as many currencies as you want
At the intersect of these is the exchange rate

The formula

=SUMPRODUCT((Sheet3!B1:M1=B4)*(Sheet3!A2:A23=C4)*( Sheet3!B2:M23))

looks in B1 to M1 and find a match for B4
looks in A2 to A23 and find a match for C4
and returns the value of the cell at the intersect

The only caveat is that if your table extends to a second year then for date
you will have to include Jan 2008 and Jan 2009 etc.

Mike

"Palle korsholm" wrote:

I have lookd at it but I might not have been clear about what i ment.

If I wright February in cell B4 and USD in cell C4 i like the exchangerate
to gent in tho d4. If i change the names in B4 and C4 I will get the new
excahgnerate

--
Palle korsholm


"Mike H" wrote:

Hi,

In this instance the table of exc rates and months is laid out like this
Col A Col B Col C Col D Col E Etc
jan feb mar apr etc
usd 1 2 1.5 1.2
euro 3 4 1.6 1.7
etc

and the formula in D4 is
=SUMPRODUCT((Sheet3!B1:M1=B4)*(Sheet3!A2:A23=C4)*( Sheet3!B2:M23))

Mike

"Palle korsholm" wrote:

In a woorkbook I have two sheets

In one sheet I got a tabel With exchange rates one coulom per month and one
row per currency
January February
USD XXX XXX
Euro XXX XXX

In the other sheet I am standing i cell D4 where I like to get the correct
exhange rate. I got one cell (B4) with the months name an an other (C4) with
the name of the currency.

Do any have an idear ?
--
Palle korsholm

Thank you for the help - this is realy usfull
--
Palle korsholm


"Mike H" wrote:

Which is exactly what the formula does.
In sheet 3 cell B1 to M1 are the months Jan- December
In column A starting in A2 are as many currencies as you want
At the intersect of these is the exchange rate

The formula

=SUMPRODUCT((Sheet3!B1:M1=B4)*(Sheet3!A2:A23=C4)*( Sheet3!B2:M23))

looks in B1 to M1 and find a match for B4
looks in A2 to A23 and find a match for C4
and returns the value of the cell at the intersect

The only caveat is that if your table extends to a second year then for date
you will have to include Jan 2008 and Jan 2009 etc.

Mike

"Palle korsholm" wrote:

I have lookd at it but I might not have been clear about what i ment.

If I wright February in cell B4 and USD in cell C4 i like the exchangerate
to gent in tho d4. If i change the names in B4 and C4 I will get the new
excahgnerate

--
Palle korsholm


"Mike H" wrote:

Hi,

In this instance the table of exc rates and months is laid out like this
Col A Col B Col C Col D Col E Etc
jan feb mar apr etc
usd 1 2 1.5 1.2
euro 3 4 1.6 1.7
etc

and the formula in D4 is
=SUMPRODUCT((Sheet3!B1:M1=B4)*(Sheet3!A2:A23=C4)*( Sheet3!B2:M23))

Mike

"Palle korsholm" wrote:

In a woorkbook I have two sheets

In one sheet I got a tabel With exchange rates one coulom per month and one
row per currency
January February
USD XXX XXX
Euro XXX XXX

In the other sheet I am standing i cell D4 where I like to get the correct
exhange rate. I got one cell (B4) with the months name an an other (C4) with
the name of the currency.

Do any have an idear ?
--
Palle korsholm

Mike,


Can you get your formula to work? The arrays in a SUMPRODUCT() must be of
the same dimensions otherwise some of the elements will not have anything to
multiply. I get #VALUE! from your formula.


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Mike H" wrote in message
...
Which is exactly what the formula does.
In sheet 3 cell B1 to M1 are the months Jan- December
In column A starting in A2 are as many currencies as you want
At the intersect of these is the exchange rate

The formula

=SUMPRODUCT((Sheet3!B1:M1=B4)*(Sheet3!A2:A23=C4)*( Sheet3!B2:M23))

looks in B1 to M1 and find a match for B4
looks in A2 to A23 and find a match for C4
and returns the value of the cell at the intersect

The only caveat is that if your table extends to a second year then for
date
you will have to include Jan 2008 and Jan 2009 etc.

Mike

"Palle korsholm" wrote:

I have lookd at it but I might not have been clear about what i ment.

If I wright February in cell B4 and USD in cell C4 i like the
exchangerate
to gent in tho d4. If i change the names in B4 and C4 I will get the new
excahgnerate

--
Palle korsholm


"Mike H" wrote:

Hi,

In this instance the table of exc rates and months is laid out like
this
Col A Col B Col C Col D Col E Etc
jan feb mar apr etc
usd 1 2 1.5 1.2
euro 3 4 1.6 1.7
etc

and the formula in D4 is
=SUMPRODUCT((Sheet3!B1:M1=B4)*(Sheet3!A2:A23=C4)*( Sheet3!B2:M23))

Mike

"Palle korsholm" wrote:

In a woorkbook I have two sheets

In one sheet I got a tabel With exchange rates one coulom per month
and one
row per currency
January February
USD XXX XXX
Euro XXX XXX

In the other sheet I am standing i cell D4 where I like to get the
correct
exhange rate. I got one cell (B4) with the months name an an other
(C4) with
the name of the currency.

Do any have an idear ?
--
Palle korsholm

Sandy,

I get the value at the intersect of month/currency

Mike

"Sandy Mann" wrote:

Mike,


Can you get your formula to work? The arrays in a SUMPRODUCT() must be of
the same dimensions otherwise some of the elements will not have anything to
multiply. I get #VALUE! from your formula.


--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Mike H" wrote in message
...
Which is exactly what the formula does.
In sheet 3 cell B1 to M1 are the months Jan- December
In column A starting in A2 are as many currencies as you want
At the intersect of these is the exchange rate

The formula

=SUMPRODUCT((Sheet3!B1:M1=B4)*(Sheet3!A2:A23=C4)*( Sheet3!B2:M23))

looks in B1 to M1 and find a match for B4
looks in A2 to A23 and find a match for C4
and returns the value of the cell at the intersect

The only caveat is that if your table extends to a second year then for
date
you will have to include Jan 2008 and Jan 2009 etc.

Mike

"Palle korsholm" wrote:

I have lookd at it but I might not have been clear about what i ment.

If I wright February in cell B4 and USD in cell C4 i like the
exchangerate
to gent in tho d4. If i change the names in B4 and C4 I will get the new
excahgnerate

--
Palle korsholm


"Mike H" wrote:

Hi,

In this instance the table of exc rates and months is laid out like
this
Col A Col B Col C Col D Col E Etc
jan feb mar apr etc
usd 1 2 1.5 1.2
euro 3 4 1.6 1.7
etc

and the formula in D4 is
=SUMPRODUCT((Sheet3!B1:M1=B4)*(Sheet3!A2:A23=C4)*( Sheet3!B2:M23))

Mike

"Palle korsholm" wrote:

In a woorkbook I have two sheets

In one sheet I got a tabel With exchange rates one coulom per month
and one
row per currency
January February
USD XXX XXX
Euro XXX XXX

In the other sheet I am standing i cell D4 where I like to get the
correct
exhange rate. I got one cell (B4) with the months name an an other
(C4) with
the name of the currency.

Do any have an idear ?
--
Palle korsholm

Try:

=HLOOKUP(B4,Sheet2!A1:M12,MATCH(C4,Sheet2!A1:A12,F ALSE),FALSE)

or with error checking:

=IF(COUNTA(B4:C4)=2,HLOOKUP(B4,Sheet2!A1:M12,MATCH (C4,Sheet2!A1:A12,FALSE),FALSE),"")

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Palle korsholm" wrote in message
...
I have lookd at it but I might not have been clear about what i ment.

If I wright February in cell B4 and USD in cell C4 i like the exchangerate
to gent in tho d4. If i change the names in B4 and C4 I will get the new
excahgnerate

--
Palle korsholm


"Mike H" wrote:

Hi,

In this instance the table of exc rates and months is laid out like this
Col A Col B Col C Col D Col E Etc
jan feb mar apr etc
usd 1 2 1.5 1.2
euro 3 4 1.6 1.7
etc

and the formula in D4 is
=SUMPRODUCT((Sheet3!B1:M1=B4)*(Sheet3!A2:A23=C4)*( Sheet3!B2:M23))

Mike

"Palle korsholm" wrote:

In a woorkbook I have two sheets

In one sheet I got a tabel With exchange rates one coulom per month and
one
row per currency
January February
USD XXX XXX
Euro XXX XXX

In the other sheet I am standing i cell D4 where I like to get the
correct
exhange rate. I got one cell (B4) with the months name an an other (C4)
with
the name of the currency.

Do any have an idear ?
--
Palle korsholm