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