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 columns containing the
same data type. Any ideas?

Without trying it I think your problem stems from the company names having
spaces in them. You need to have single quotes around the Sheet name...

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


--
HTH...

Jim Thomlinson


"cvach" wrote:

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 columns containing the
same data type. Any ideas?