(Msg. 1) Posted: Tue Aug 05, 2008 7:45 am
Post subject: Extracting Data from another worksheet based on user input Add to elertz Archived from groups: microsoft>public>excel>worksheet>functions (more info?)
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.
(Msg. 2) Posted: Tue Aug 05, 2008 5:23 pm
Post subject: Re: Extracting Data from another worksheet based on user input Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
"Alvyn" <Alvyn.RemoveThis@discussions.microsoft.com> wrote in message
news:E679174B-68E3-416F-A08B-8FEE51FA2BF0@microsoft.com...
>
> 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
sandymann2.RemoveThis@mailinator.com
Replace @mailinator.com with @tiscali.co.uk
(Msg. 3) Posted: Tue Aug 05, 2008 5:23 pm
Post subject: Re: Extracting Data from another worksheet based on user input Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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" <Alvyn DeleteThis @discussions.microsoft.com> wrote in message
> news:E679174B-68E3-416F-A08B-8FEE51FA2BF0@microsoft.com...
> >
> > 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
>
> sandymann2 DeleteThis @mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "
>
>
>
(Msg. 4) Posted: Tue Aug 05, 2008 6:05 pm
Post subject: Re: Extracting Data from another worksheet based on user input Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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:
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
sandymann2.TakeThisOut@mailinator.com
Replace @mailinator.com with @tiscali.co.uk
"Alvyn" <Alvyn.TakeThisOut@discussions.microsoft.com> wrote in message
news:DEC4C904-B044-4673-A9BB-46464BD83CB6@microsoft.com...
> 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" <Alvyn.TakeThisOut@discussions.microsoft.com> wrote in message
>> news:E679174B-68E3-416F-A08B-8FEE51FA2BF0@microsoft.com...
>> >
>> > 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
>>
>> sandymann2.TakeThisOut@mailinator.com
>> Replace @mailinator.com with @tiscali.co.uk
>>
>>
>> "
>>
>>
>>
>
(Msg. 5) Posted: Tue Aug 05, 2008 6:05 pm
Post subject: Re: Extracting Data from another worksheet based on user input Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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!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
>
> sandymann2 RemoveThis @mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "Alvyn" <Alvyn RemoveThis @discussions.microsoft.com> wrote in message
> news:DEC4C904-B044-4673-A9BB-46464BD83CB6@microsoft.com...
> > 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" <Alvyn RemoveThis @discussions.microsoft.com> wrote in message
> >> news:E679174B-68E3-416F-A08B-8FEE51FA2BF0@microsoft.com...
> >> >
> >> > 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
> >>
> >> sandymann2 RemoveThis @mailinator.com
> >> Replace @mailinator.com with @tiscali.co.uk
> >>
> >>
> >> "
> >>
> >>
> >>
> >
>
>
>
(Msg. 6) Posted: Tue Aug 05, 2008 6:36 pm
Post subject: Re: Extracting Data from another worksheet based on user input Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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
sandymann2.RemoveThis@mailinator.com
Replace @mailinator.com with @tiscali.co.uk
"Alvyn" <Alvyn.RemoveThis@discussions.microsoft.com> wrote in message
news:4B32EAA6-842F-4D79-9675-3378E3EDA8D6@microsoft.com...
> 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!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
>>
>> sandymann2.RemoveThis@mailinator.com
>> Replace @mailinator.com with @tiscali.co.uk
>>
>>
>> "Alvyn" <Alvyn.RemoveThis@discussions.microsoft.com> wrote in message
>> news:DEC4C904-B044-4673-A9BB-46464BD83CB6@microsoft.com...
>> > 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" <Alvyn.RemoveThis@discussions.microsoft.com> wrote in message
>> >> news:E679174B-68E3-416F-A08B-8FEE51FA2BF0@microsoft.com...
>> >> >
>> >> > 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
>> >>
>> >> sandymann2.RemoveThis@mailinator.com
>> >> Replace @mailinator.com with @tiscali.co.uk
>> >>
>> >>
>> >> "
>> >>
>> >>
>> >>
>> >
>>
>>
>>
>
(Msg. 7) Posted: Tue Aug 05, 2008 7:47 pm
Post subject: Re: Extracting Data from another worksheet based on user input Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Dear Sandy
OK. Now it works. Thanks for your precious advice.
Alvyn
"Sandy Mann" wrote:
> 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
>
> sandymann2 DeleteThis @mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "Alvyn" <Alvyn DeleteThis @discussions.microsoft.com> wrote in message
> news:4B32EAA6-842F-4D79-9675-3378E3EDA8D6@microsoft.com...
> > 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!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
> >>
> >> sandymann2 DeleteThis @mailinator.com
> >> Replace @mailinator.com with @tiscali.co.uk
> >>
> >>
> >> "Alvyn" <Alvyn DeleteThis @discussions.microsoft.com> wrote in message
> >> news:DEC4C904-B044-4673-A9BB-46464BD83CB6@microsoft.com...
> >> > 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" <Alvyn DeleteThis @discussions.microsoft.com> wrote in message
> >> >> news:E679174B-68E3-416F-A08B-8FEE51FA2BF0@microsoft.com...
> >> >> >
> >> >> > 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
> >> >>
> >> >> sandymann2 DeleteThis @mailinator.com
> >> >> Replace @mailinator.com with @tiscali.co.uk
> >> >>
> >> >>
> >> >> "
> >> >>
> >> >>
> >> >>
> >> >
> >>
> >>
> >>
> >
>
>
>
(Msg. 8) Posted: Tue Aug 05, 2008 7:52 pm
Post subject: Re: Extracting Data from another worksheet based on user input Add to elertz [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Dear Sandy
In the INDEX(array, row_num, col_num) formula, can I replace the row_num &
col_num with a variable?
Thanks
Alvyn
"Sandy Mann" wrote:
> 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
>
> sandymann2 DeleteThis @mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
>
>
> "Alvyn" <Alvyn DeleteThis @discussions.microsoft.com> wrote in message
> news:4B32EAA6-842F-4D79-9675-3378E3EDA8D6@microsoft.com...
> > 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!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
> >>
> >> sandymann2 DeleteThis @mailinator.com
> >> Replace @mailinator.com with @tiscali.co.uk
> >>
> >>
> >> "Alvyn" <Alvyn DeleteThis @discussions.microsoft.com> wrote in message
> >> news:DEC4C904-B044-4673-A9BB-46464BD83CB6@microsoft.com...
> >> > 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" <Alvyn DeleteThis @discussions.microsoft.com> wrote in message
> >> >> news:E679174B-68E3-416F-A08B-8FEE51FA2BF0@microsoft.com...
> >> >> >
> >> >> > 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
> >> >>
> >> >> sandymann2 DeleteThis @mailinator.com
> >> >> Replace @mailinator.com with @tiscali.co.uk
> >> >>
> >> >>
> >> >> "
> >> >>
> >> >>
> >> >>
> >> >
> >>
> >>
> >>
> >
>
>
>
All times are: Eastern Time (US & Canada) (change) Goto page 1, 2
Page 1 of 2
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Search
Profile
Private Messages
Log in/Register/Password
Office