Hello,

I have the following issue. I have created two worksheets (ws). The first
one contains several individual bonds with in the adjacent columns price
information, yields, and so on. In the second one I have made a list
(validated) which refers to the list of bonds in the first ws. When I choose
a bondtitle in the second ws, I would like to get the price, yield and so on
which correspond to the chosen title in the adjacent columns. When I use the
following formula I get an error message:

=VLOOKUP($B9;Data!$A:$W;COLUMN(Data!Q18);FALSE)

$B9 = Bondtitle
Data!$A:$W=matrix (first ws)
COLUMN(Data!Q18) = column in first ws with the prices of the individual bonds

The matrix is not sorted, so I use the False component. Does anyone have a
clue why I get an error message.

Thanx.

Noepie

Hi,
try
=Sumproduct(--(Data!$A:$W=B9),--(Data!Q:Q))

"Noepie" wrote:

Hello,

I have the following issue. I have created two worksheets (ws). The first
one contains several individual bonds with in the adjacent columns price
information, yields, and so on. In the second one I have made a list
(validated) which refers to the list of bonds in the first ws. When I choose
a bondtitle in the second ws, I would like to get the price, yield and so on
which correspond to the chosen title in the adjacent columns. When I use the
following formula I get an error message:

=VLOOKUP($B9;Data!$A:$W;COLUMN(Data!Q18);FALSE)

$B9 = Bondtitle
Data!$A:$W=matrix (first ws)
COLUMN(Data!Q18) = column in first ws with the prices of the individual bonds

The matrix is not sorted, so I use the False component. Does anyone have a
clue why I get an error message.

Thanx.

Noepie

What error message do you get?

--
Regards
Roger Govier

"Noepie" wrote in message
...
Hello,

I have the following issue. I have created two worksheets (ws). The first
one contains several individual bonds with in the adjacent columns price
information, yields, and so on. In the second one I have made a list
(validated) which refers to the list of bonds in the first ws. When I
choose
a bondtitle in the second ws, I would like to get the price, yield and so
on
which correspond to the chosen title in the adjacent columns. When I use
the
following formula I get an error message:

=VLOOKUP($B9;Data!$A:$W;COLUMN(Data!Q18);FALSE)

$B9 = Bondtitle
Data!$A:$W=matrix (first ws)
COLUMN(Data!Q18) = column in first ws with the prices of the individual
bonds

The matrix is not sorted, so I use the False component. Does anyone have a
clue why I get an error message.

Thanx.

Noepie

I have a Dutch excel version. The error message is: #N/B. I think the
equivalent in English will be #N/A. Leaving False out of the formula results
in one of the prices of the bond list but not the one which corresponds with
the chosen title.

Noepie

"Roger Govier" wrote:

What error message do you get?

--
Regards
Roger Govier

"Noepie" wrote in message
...
Hello,

I have the following issue. I have created two worksheets (ws). The first
one contains several individual bonds with in the adjacent columns price
information, yields, and so on. In the second one I have made a list
(validated) which refers to the list of bonds in the first ws. When I
choose
a bondtitle in the second ws, I would like to get the price, yield and so
on
which correspond to the chosen title in the adjacent columns. When I use
the
following formula I get an error message:

=VLOOKUP($B9;Data!$A:$W;COLUMN(Data!Q18);FALSE)

$B9 = Bondtitle
Data!$A:$W=matrix (first ws)
COLUMN(Data!Q18) = column in first ws with the prices of the individual
bonds

The matrix is not sorted, so I use the False component. Does anyone have a
clue why I get an error message.

Thanx.

Noepie

Hi

Then the error is created because the name of that Bond doesn't exist in the
list.
Perhaps there are some extra spaces either in the cell B9, or in the list
in column A of Data.

If the name of the bond is in Data, then check the length of its cell and
compare with the length of cell b9
=LEN(B9)

--
Regards
Roger Govier

"Noepie" wrote in message
...
I have a Dutch excel version. The error message is: #N/B. I think the
equivalent in English will be #N/A. Leaving False out of the formula
results
in one of the prices of the bond list but not the one which corresponds
with
the chosen title.

Noepie

"Roger Govier" wrote:

What error message do you get?

--
Regards
Roger Govier

"Noepie" wrote in message
...
Hello,

I have the following issue. I have created two worksheets (ws). The
first
one contains several individual bonds with in the adjacent columns
price
information, yields, and so on. In the second one I have made a list
(validated) which refers to the list of bonds in the first ws. When I
choose
a bondtitle in the second ws, I would like to get the price, yield and
so
on
which correspond to the chosen title in the adjacent columns. When I
use
the
following formula I get an error message:

=VLOOKUP($B9;Data!$A:$W;COLUMN(Data!Q18);FALSE)

$B9 = Bondtitle
Data!$A:$W=matrix (first ws)
COLUMN(Data!Q18) = column in first ws with the prices of the individual
bonds

The matrix is not sorted, so I use the False component. Does anyone
have a
clue why I get an error message.

Thanx.

Noepie

Hi,

I checked the length of the cells, they are the same. I didn't expect this.
With Data- validation I created the list in the second ws. While this list
makes use of the names in the 1st ws there shouldn't be any difference. The
lenght check confirms this. I created this list with validation because when
I change in the 1st ws this change will also be in the validation list of the
2nd ws. Maybe, this is not the proper way to day. I hope there any other
possibilities.

Kind regards,

Noepie

"Roger Govier" wrote:

Hi

Then the error is created because the name of that Bond doesn't exist in the
list.
Perhaps there are some extra spaces either in the cell B9, or in the list
in column A of Data.

If the name of the bond is in Data, then check the length of its cell and
compare with the length of cell b9
=LEN(B9)

--
Regards
Roger Govier

"Noepie" wrote in message
...
I have a Dutch excel version. The error message is: #N/B. I think the
equivalent in English will be #N/A. Leaving False out of the formula
results
in one of the prices of the bond list but not the one which corresponds
with
the chosen title.

Noepie

"Roger Govier" wrote:

What error message do you get?

--
Regards
Roger Govier

"Noepie" wrote in message
...
Hello,

I have the following issue. I have created two worksheets (ws). The
first
one contains several individual bonds with in the adjacent columns
price
information, yields, and so on. In the second one I have made a list
(validated) which refers to the list of bonds in the first ws. When I
choose
a bondtitle in the second ws, I would like to get the price, yield and
so
on
which correspond to the chosen title in the adjacent columns. When I
use
the
following formula I get an error message:

=VLOOKUP($B9;Data!$A:$W;COLUMN(Data!Q18);FALSE)

$B9 = Bondtitle
Data!$A:$W=matrix (first ws)
COLUMN(Data!Q18) = column in first ws with the prices of the individual
bonds

The matrix is not sorted, so I use the False component. Does anyone
have a
clue why I get an error message.

Thanx.

Noepie

Then I can't see why you are not getting the result.
Would you like me to look at the file for you to see if i can see what is
going wrong?
If so, to mail direct
roger at technology4u dot co dot uk
Change at and dots to make valid email address

--
Regards
Roger Govier

"Noepie" wrote in message
...
Hi,

I checked the length of the cells, they are the same. I didn't expect
this.
With Data- validation I created the list in the second ws. While this
list
makes use of the names in the 1st ws there shouldn't be any difference.
The
lenght check confirms this. I created this list with validation because
when
I change in the 1st ws this change will also be in the validation list of
the
2nd ws. Maybe, this is not the proper way to day. I hope there any other
possibilities.

Kind regards,

Noepie

"Roger Govier" wrote:

Hi

Then the error is created because the name of that Bond doesn't exist in
the
list.
Perhaps there are some extra spaces either in the cell B9, or in the
list
in column A of Data.

If the name of the bond is in Data, then check the length of its cell and
compare with the length of cell b9
=LEN(B9)

--
Regards
Roger Govier

"Noepie" wrote in message
...
I have a Dutch excel version. The error message is: #N/B. I think the
equivalent in English will be #N/A. Leaving False out of the formula
results
in one of the prices of the bond list but not the one which corresponds
with
the chosen title.

Noepie

"Roger Govier" wrote:

What error message do you get?

--
Regards
Roger Govier

"Noepie" wrote in message
...
Hello,

I have the following issue. I have created two worksheets (ws). The
first
one contains several individual bonds with in the adjacent columns
price
information, yields, and so on. In the second one I have made a list
(validated) which refers to the list of bonds in the first ws. When
I
choose
a bondtitle in the second ws, I would like to get the price, yield
and
so
on
which correspond to the chosen title in the adjacent columns. When I
use
the
following formula I get an error message:

=VLOOKUP($B9;Data!$A:$W;COLUMN(Data!Q18);FALSE)

$B9 = Bondtitle
Data!$A:$W=matrix (first ws)
COLUMN(Data!Q18) = column in first ws with the prices of the
individual
bonds

The matrix is not sorted, so I use the False component. Does anyone
have a
clue why I get an error message.

Thanx.

Noepie

Hi Roger,

I just send you the file!

Thanx.

"Roger Govier" wrote:

Then I can't see why you are not getting the result.
Would you like me to look at the file for you to see if i can see what is
going wrong?
If so, to mail direct
roger at technology4u dot co dot uk
Change at and dots to make valid email address

--
Regards
Roger Govier

"Noepie" wrote in message
...
Hi,

I checked the length of the cells, they are the same. I didn't expect
this.
With Data- validation I created the list in the second ws. While this
list
makes use of the names in the 1st ws there shouldn't be any difference.
The
lenght check confirms this. I created this list with validation because
when
I change in the 1st ws this change will also be in the validation list of
the
2nd ws. Maybe, this is not the proper way to day. I hope there any other
possibilities.

Kind regards,

Noepie

"Roger Govier" wrote:

Hi

Then the error is created because the name of that Bond doesn't exist in
the
list.
Perhaps there are some extra spaces either in the cell B9, or in the
list
in column A of Data.

If the name of the bond is in Data, then check the length of its cell and
compare with the length of cell b9
=LEN(B9)

--
Regards
Roger Govier

"Noepie" wrote in message
...
I have a Dutch excel version. The error message is: #N/B. I think the
equivalent in English will be #N/A. Leaving False out of the formula
results
in one of the prices of the bond list but not the one which corresponds
with
the chosen title.

Noepie

"Roger Govier" wrote:

What error message do you get?

--
Regards
Roger Govier

"Noepie" wrote in message
...
Hello,

I have the following issue. I have created two worksheets (ws). The
first
one contains several individual bonds with in the adjacent columns
price
information, yields, and so on. In the second one I have made a list
(validated) which refers to the list of bonds in the first ws. When
I
choose
a bondtitle in the second ws, I would like to get the price, yield
and
so
on
which correspond to the chosen title in the adjacent columns. When I
use
the
following formula I get an error message:

=VLOOKUP($B9;Data!$A:$W;COLUMN(Data!Q18);FALSE)

$B9 = Bondtitle
Data!$A:$W=matrix (first ws)
COLUMN(Data!Q18) = column in first ws with the prices of the
individual
bonds

The matrix is not sorted, so I use the False component. Does anyone
have a
clue why I get an error message.

Thanx.

Noepie