Dear Sir/Mdm,

Can anyone advise me how to perform the following in Excel?

On worksheet B, I have a list of data in column A.
Eg, A1=Holland, A2=England, A3=New Zealand, A4=Australia, A5=Asia.

What I want to achieve is whenever I type any key in worksheet A on a Cell
B2, it is able to lookup the information from A1 to A5 in worksheet B and
display the value in Cell B2 of worksheet A.

Please advise and many thanks.

Alvyn

"Alvyn" wrote in message
...

What I want to achieve is whenever I type any key in worksheet A on a Cell
B2, it is able to lookup the information from A1 to A5 in worksheet B and
display the value in Cell B2 of worksheet A.

What is it that you are typing in worksheet A?

--

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


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


"

Dear Sandy

In worksheet A, I will input any value in Cell B2. If it matches a value in
the range A1 to A5 in Worksheet B, it will then display that value in Cell B2
in worksheet A.

In another words, if I type "Aus" or "Australia", it will search the range
A1 to A5 in worksheet B and return a value on Cell B2 in Worksheet A with the
value "Australia".

I wonder is this possible? Otherwise, can this be done using combo box? and
how should I go about it?

Thanks


Alvyn

"Sandy Mann" wrote:

"Alvyn" wrote in message
...

What I want to achieve is whenever I type any key in worksheet A on a Cell
B2, it is able to lookup the information from A1 to A5 in worksheet B and
display the value in Cell B2 of worksheet A.

What is it that you are typing in worksheet A?

--

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


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


"

I'm afraid that you have lost me there.

In worksheet A, I will input any value in Cell B2.

In another words, if I type "Aus" or "Australia",
...................... and return a value on Cell B2 in Worksheet A with
the
value "Australia".

Isn't it already there?

To get "Aus" to change to "Australia" in the same cell then you would need
to use VBA.

To get the name in - say C2 - then you could use the formula:

=INDEX(Sheet2!A1:A5,MATCH(LEFT(B2,3),LEFT(Sheet2!A 1:A5,3),0))

This is an array formula which should be entered using Ctrl + Shift + Enter
not just Enter on its own.

--
HTH

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


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


"Alvyn" wrote in message
...
Dear Sandy

In worksheet A, I will input any value in Cell B2. If it matches a value
in
the range A1 to A5 in Worksheet B, it will then display that value in Cell
B2
in worksheet A.

In another words, if I type "Aus" or "Australia", it will search the range
A1 to A5 in worksheet B and return a value on Cell B2 in Worksheet A with
the
value "Australia".

I wonder is this possible? Otherwise, can this be done using combo box?
and
how should I go about it?

Thanks


Alvyn

"Sandy Mann" wrote:

"Alvyn" wrote in message
...

What I want to achieve is whenever I type any key in worksheet A on a
Cell
B2, it is able to lookup the information from A1 to A5 in worksheet B
and
display the value in Cell B2 of worksheet A.

What is it that you are typing in worksheet A?

--

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


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


"

Dear Sandy

Thanks for the reply. Howvever, I still can't get it to work.

Anyway, I will try again.

Thank you very much

Alvyn

"Sandy Mann" wrote:

I'm afraid that you have lost me there.

In worksheet A, I will input any value in Cell B2.

In another words, if I type "Aus" or "Australia",
...................... and return a value on Cell B2 in Worksheet A with
the
value "Australia".

Isn't it already there?

To get "Aus" to change to "Australia" in the same cell then you would need
to use VBA.

To get the name in - say C2 - then you could use the formula:

=INDEX(Sheet2!A1:A5,MATCH(LEFT(B2,3),LEFT(Sheet2!A 1:A5,3),0))

This is an array formula which should be entered using Ctrl + Shift + Enter
not just Enter on its own.

--
HTH

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


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


"Alvyn" wrote in message
...
Dear Sandy

In worksheet A, I will input any value in Cell B2. If it matches a value
in
the range A1 to A5 in Worksheet B, it will then display that value in Cell
B2
in worksheet A.

In another words, if I type "Aus" or "Australia", it will search the range
A1 to A5 in worksheet B and return a value on Cell B2 in Worksheet A with
the
value "Australia".

I wonder is this possible? Otherwise, can this be done using combo box?
and
how should I go about it?

Thanks


Alvyn

"Sandy Mann" wrote:

"Alvyn" wrote in message
...

What I want to achieve is whenever I type any key in worksheet A on a
Cell
B2, it is able to lookup the information from A1 to A5 in worksheet B
and
display the value in Cell B2 of worksheet A.

What is it that you are typing in worksheet A?

--

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


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


"

Did you enter the formula by holding down the Ctrl & Shift keys while you
pressed the Enter key?

--
HTH

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


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


"Alvyn" wrote in message
...
Dear Sandy

Thanks for the reply. Howvever, I still can't get it to work.

Anyway, I will try again.

Thank you very much

Alvyn

"Sandy Mann" wrote:

I'm afraid that you have lost me there.

In worksheet A, I will input any value in Cell B2.

In another words, if I type "Aus" or "Australia",
...................... and return a value on Cell B2 in Worksheet A
with
the
value "Australia".

Isn't it already there?

To get "Aus" to change to "Australia" in the same cell then you would
need
to use VBA.

To get the name in - say C2 - then you could use the formula:

=INDEX(Sheet2!A1:A5,MATCH(LEFT(B2,3),LEFT(Sheet2!A 1:A5,3),0))

This is an array formula which should be entered using Ctrl + Shift +
Enter
not just Enter on its own.

--
HTH

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


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


"Alvyn" wrote in message
...
Dear Sandy

In worksheet A, I will input any value in Cell B2. If it matches a
value
in
the range A1 to A5 in Worksheet B, it will then display that value in
Cell
B2
in worksheet A.

In another words, if I type "Aus" or "Australia", it will search the
range
A1 to A5 in worksheet B and return a value on Cell B2 in Worksheet A
with
the
value "Australia".

I wonder is this possible? Otherwise, can this be done using combo box?
and
how should I go about it?

Thanks


Alvyn

"Sandy Mann" wrote:

"Alvyn" wrote in message
...

What I want to achieve is whenever I type any key in worksheet A on
a
Cell
B2, it is able to lookup the information from A1 to A5 in worksheet
B
and
display the value in Cell B2 of worksheet A.

What is it that you are typing in worksheet A?

--

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


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


"