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

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
[email protected]
"dharmik" <[email protected]> wrote in
message news:[email protected]...
>
> 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

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

--
Don Guillett
SalesAid Software
[email protected]
"dharmik" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
[email protected]
"dharmik" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

--
Don Guillett
SalesAid Software
[email protected]
"dharmik" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
>
>

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

--
Don Guillett
SalesAid Software
[email protected]
"dharmik" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
[email protected]
"dharmik" <[email protected]> wrote in
message news:[email protected]...
>
> 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
[email protected]
"dharmik" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

--
Don Guillett
SalesAid Software
[email protected]
"dharmik" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
>
>

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

--
Don Guillett
SalesAid Software
[email protected]
"dharmik" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
[email protected]
"dharmik" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

--
Don Guillett
SalesAid Software
[email protected]
"dharmik" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
[email protected]
"dharmik" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>

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

--
Don Guillett
SalesAid Software
[email protected]
"dharmik" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
[email protected]
"dharmik" <[email protected]> wrote in
message news:[email protected]...
>
> 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
[email protected]
"dharmik" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

--
Don Guillett
SalesAid Software
[email protected]
"dharmik" <[email protected]> wrote in
message news:[email protected]...
>
> 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
[email protected]
"dharmik" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
[email protected]
"dharmik" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>

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

--
Don Guillett
SalesAid Software
[email protected]
"dharmik" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
[email protected]
"dharmik" <[email protected]> wrote in
message news:[email protected]...
>
> 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
[email protected]
"dharmik" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

--
Don Guillett
SalesAid Software
[email protected]
"dharmik" <[email protected]> wrote in
message news:[email protected]...
>
> 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
[email protected]
"dharmik" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
[email protected]
"dharmik" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>
>

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
[email protected]
"dharmik" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

--
Don Guillett
SalesAid Software
[email protected]
"dharmik" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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
[email protected]
"dharmik" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

--
Don Guillett
SalesAid Software
[email protected]
"dharmik" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>