Extracting Data from another worksheet based on user input

[Alvyn]

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

 

[Sandy Mann]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk


"

 

[Alvyn]

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]

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!A1: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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Alvyn]

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]

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Alvyn]

Dear Sandy

OK. Now it works. Thanks for your precious advice.



Alvyn

 

[Alvyn]

Dear Sandy

In the INDEX(array, row_num, col_num) formula, can I replace the row_num &
col_num with a variable?

Thanks

Alvyn

 

[Alvyn]

Dear Sandy,

In the INDEX(array, row_num, col_num) formula, can I replace the row_num
and/or col_num with a variable?

Thanks

Alvyn

 

[Sandy Mann]

I don't really understand what you mean by *variable*

Actually I am not using the col_num argument, (the
MATCH(LEFT(B2,3),LEFT(Sheet2!A1:A5,3),0) returns *one* number, is that what
you meant by variable?), so the col_num argumant defaults to 1.

If you meant a cell relerence then yes, you can write it as:

=INDEX(Sheet2!A1:A5,B1,C1)

What is it that you are trying to do?

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Alvyn]

Dear Sandy

My "variable" means, a temporary space in memory.

Eg, ROW_ENTRY & COL_ENTRY are variables and its value is determine by the
input of certain value in a specific cell, say B2 & C2.

Therefore, if I enter "10" in cell B2 and "20" in cell C2, ROW_ENTRY will
hold the value 10 and COL_ENTRY will hold the value 20 respectively.

So, if I use INDEX(array, row_num, col_num) formula, it will achieve
something like:
INDEX(array, 10, 20)

And if I enter "A" in cell B2 and "B" in cell C2, ROW_ENTRY will hold the
value A and COL_ENTRY will hold the value B respectively.

And the result will look something like :
INDEX(array, A, B)

Is this possible in any of the EXCEL's functions, formula etc?

Thanks

Alvyn

 

[Sandy Mann]

My "variable" means, a temporary space in memory.

I've been calling that a *senior moment* <VBG>

You can Define Names in Excel (Insert > Name > Define) - say you use your A
& B - and give the names a value then you those names in the function like:

=INDEX(A1:B10,A,B)

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

I forgot to point out that you can define A & B but not C because C is a
reserved name in XL ( for Column)

--
HTH

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Alvyn]

Dear Sandy

Thank you very much. You advice has saved me many nights cracking my head
and web searches and I really appreciate it.

Once again thank you.

Sincerely,

Alvyn

 

[Sandy Mann]

Your are very welcome. Thanks for posting back

--

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk