I have a worksheet that contains a cell that I enter in different
company names such as (Costco, Sams Club, Jack n the Box). Each of
these companys has a seperate worksheet with their addresses that I
want to lookup. My question is: Can you create a macro that takes the
name in the "Company" cell and uses that name to refer to the
appropriate worksheet? And from their can you then use a lookup
function to lookup the appropriate data.

Assume the worksheet for Costco is named Costco

if Costco is entered in A1, then

=if(A1<"",Indirect(A1&"!B9"),"")

would return the value found in cell B9 of the Sheet Costco

Similarly that technique could be used with a vlookup function

=if(Or(A1="",A2=""),"",Vlookup(A2,Indirect(A1&"!A2 :Z200"),3,False))

--
Regards,
Tom Ogilvy


"cvach" wrote in message
oups.com...
I have a worksheet that contains a cell that I enter in different
company names such as (Costco, Sams Club, Jack n the Box). Each of
these companys has a seperate worksheet with their addresses that I
want to lookup. My question is: Can you create a macro that takes the
name in the "Company" cell and uses that name to refer to the
appropriate worksheet? And from their can you then use a lookup
function to lookup the appropriate data.

=IF(OR($B$3="",$A$8=""),"",VLOOKUP($A$8,INDIRECT($ B$3&"!A1:Z200"),2,FALSE))


This is the Formula that I have imputed into the cells I want looking
up information. This formula works perfectly with the Costco worksheet
but I recieved a #REF error when I try any other worksheet.

B3 contains the company name
A8 contains the location #

I also formated all of the customer worksheets the same with all of the
colums containing the same data type.

Any ideas?

If the sheet names have spaces in them you need this addition:

=IF(OR($B$3="",$A$8=""),"",VLOOKUP($A$8,INDIRECT(" '"&$B$3&"'!A1:Z200"),2,FAL
SE))

so that the sheet name will be included in single quotes. Note there were
additions on both sides of the $B$3 in the above.

--
Regards,
Tom Ogilvy


"cvach" wrote in message
oups.com...

=IF(OR($B$3="",$A$8=""),"",VLOOKUP($A$8,INDIRECT($ B$3&"!A1:Z200"),2,FALSE))


This is the Formula that I have imputed into the cells I want looking
up information. This formula works perfectly with the Costco worksheet
but I recieved a #REF error when I try any other worksheet.

B3 contains the company name
A8 contains the location #

I also formated all of the customer worksheets the same with all of the
colums containing the same data type.

Any ideas?