How do you Lookup data on different worksheets

[cvach]

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.

 

[Tom Ogilvy]

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

 

[cvach]

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

 

[Tom Ogilvy]

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.