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

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

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

So do I on a fresh workbook, I don't know what was wrong - my apologies.
However, I see that you are multiplying a Row by a Column - don't tell Bob
Philips - from his page on SUMPRODUCT():

*********************************
In a SUMPRODUCT function, the arrays being evaluated cannot be a mix of
column and row ranges, they must all be columns, or all rows.
*********************************

It seems to me that it works because there is no possibility that the can be
duplicate values in the first two arrays and the third array is a mixture of
Rows & Columns. ( ie either all columns or all rows in the third array
produces a #N/A error).

It obviously won't matter very much here but I wonder what the calculation
overhead would be with large arrays using this method.


--
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
...
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

Sandy,

Apologies aren't necessary. I've used this sucessfully many times and don't
believe it breaks any rules concerning sumproduct ranges, in my simplistic
view of things all rows are the same length as are all columns. I don't
understand the BP comment you note below because it does seem to suggest that
this shouldn't work but perhaps there are ways in which sumproduct will fall
over if used like this.

Incidentally it works perfectly well if you have duplicates in either/both
of the first 2 arrays. Stick 2 january's in and it sums both, hence my caveat
in my original response to the OP about changing the date to jan 2008 or 9.

Mike

"Sandy Mann" wrote:

So do I on a fresh workbook, I don't know what was wrong - my apologies.
However, I see that you are multiplying a Row by a Column - don't tell Bob
Philips - from his page on SUMPRODUCT():

*********************************
In a SUMPRODUCT function, the arrays being evaluated cannot be a mix of
column and row ranges, they must all be columns, or all rows.
*********************************

It seems to me that it works because there is no possibility that the can be
duplicate values in the first two arrays and the third array is a mixture of
Rows & Columns. ( ie either all columns or all rows in the third array
produces a #N/A error).

It obviously won't matter very much here but I wonder what the calculation
overhead would be with large arrays using this method.


--
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
...
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

The Rows x Columns issue highlights one of the
differences between the two SUMPRODUCT constructs:
=SUMPRODUCT(rng1,rng2)
vs
=SUMPRODUCT(rng1*rng2)

With
A1: (blank)
A2:A6 containing: 1; 2; 3; 4; 5
B1:D1 containing: 10; 20; 30

This formula fails:
=SUMPRODUCT(A2:A6,B1:D1)...returns #VALUE!

where this formula succeeds:
=SUMPRODUCT(A2:A6*B1:D1)...returns 900

--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)


"Sandy Mann" wrote in message
...
So do I on a fresh workbook, I don't know what was wrong - my apologies.
However, I see that you are multiplying a Row by a Column - don't tell Bob
Philips - from his page on SUMPRODUCT():

*********************************
In a SUMPRODUCT function, the arrays being evaluated cannot be a mix of
column and row ranges, they must all be columns, or all rows.
*********************************

It seems to me that it works because there is no possibility that the can
be duplicate values in the first two arrays and the third array is a
mixture of Rows & Columns. ( ie either all columns or all rows in the
third array produces a #N/A error).

It obviously won't matter very much here but I wonder what the calculation
overhead would be with large arrays using this method.


--
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
...
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