Question on Vlookup
 

I am setting up a Quality check worksheet.
I have one sheet each for every supplier . These sheets contain article
number for each product they supply and respective dimensions and other
detials of each product.
On the Quality check sheet i have article number, name of supplier and
all the detials i need for the article number i select.
for each detail i am using a vlookup function, where it looks up
detail for the article number i have chosen.
I have named ranges for each supplier sheet by the name of the
supplier.

I want to set up the quality sheet in such a way that once i select teh
supplier name in this sheet, all the vlookup funtions in this sheet will
take data from the sheet of the supplier name i selected. Hence if i had
to add new suppliers later, it is included automatically.

I have name the ranges for each supplier sheet by the name of supplier,
so i was wondering how can vlookup take up range names automaticaly with
respect to the supplier name i select in the quality sheet.

If this does not work, can it be done in some other way.

thanks for all help,

dharmik


--
dharmik
------------------------------------------------------------------------
dharmik's Profile: http://www.excelforum.com/member.php...o&userid=25463
View this thread: http://www.excelforum.com/showthread...hreadid=389037

Assuming your supplier's range name is in cell B9 and the article # is in A9

=VLOOKUP(A9,INDIRECT(B9),2,FALSE)

"dharmik" wrote:


I am setting up a Quality check worksheet.
I have one sheet each for every supplier . These sheets contain article
number for each product they supply and respective dimensions and other
detials of each product.
On the Quality check sheet i have article number, name of supplier and
all the detials i need for the article number i select.
for each detail i am using a vlookup function, where it looks up
detail for the article number i have chosen.
I have named ranges for each supplier sheet by the name of the
supplier.

I want to set up the quality sheet in such a way that once i select teh
supplier name in this sheet, all the vlookup funtions in this sheet will
take data from the sheet of the supplier name i selected. Hence if i had
to add new suppliers later, it is included automatically.

I have name the ranges for each supplier sheet by the name of supplier,
so i was wondering how can vlookup take up range names automaticaly with
respect to the supplier name i select in the quality sheet.

If this does not work, can it be done in some other way.

thanks for all help,

dharmik


--
dharmik
------------------------------------------------------------------------
dharmik's Profile: http://www.excelforum.com/member.php...o&userid=25463
View this thread: http://www.excelforum.com/showthread...hreadid=389037

you could use an indirect formula where e4 has the sheet name
=VLOOKUP(2,INDIRECT(E4&"!$H$1:$I$4"),2)
then you could use a worksheet_selection event to make e4 become the
selection.

--
Don Guillett
SalesAid Software

"dharmik" wrote in
message ...

I am setting up a Quality check worksheet.
I have one sheet each for every supplier . These sheets contain article
number for each product they supply and respective dimensions and other
detials of each product.
On the Quality check sheet i have article number, name of supplier and
all the detials i need for the article number i select.
for each detail i am using a vlookup function, where it looks up
detail for the article number i have chosen.
I have named ranges for each supplier sheet by the name of the
supplier.

I want to set up the quality sheet in such a way that once i select teh
supplier name in this sheet, all the vlookup funtions in this sheet will
take data from the sheet of the supplier name i selected. Hence if i had
to add new suppliers later, it is included automatically.

I have name the ranges for each supplier sheet by the name of supplier,
so i was wondering how can vlookup take up range names automaticaly with
respect to the supplier name i select in the quality sheet.

If this does not work, can it be done in some other way.

thanks for all help,

dharmik


--
dharmik
------------------------------------------------------------------------
dharmik's Profile:
http://www.excelforum.com/member.php...o&userid=25463
View this thread: http://www.excelforum.com/showthread...hreadid=389037

I thank
Don and Duke for answering my question and helping me get solution to
my problem.
Yes Indirect function is the answer to my problem. and i got my stuff
done.

Once again thank you for your quick response.

Dharmik


--
dharmik
------------------------------------------------------------------------
dharmik's Profile: http://www.excelforum.com/member.php...o&userid=25463
View this thread: http://www.excelforum.com/showthread...hreadid=389037

glad to help. Isn't it better when you do most of it yourself?

--
Don Guillett
SalesAid Software

"dharmik" wrote in
message ...

I thank
Don and Duke for answering my question and helping me get solution to
my problem.
Yes Indirect function is the answer to my problem. and i got my stuff
done.

Once again thank you for your quick response.

Dharmik


--
dharmik
------------------------------------------------------------------------
dharmik's Profile:
http://www.excelforum.com/member.php...o&userid=25463
View this thread: http://www.excelforum.com/showthread...hreadid=389037