How do you Lookup data on different worksheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

cvach - 24 Jan 2006 15:53 GMT

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.

Reply to this Message

Tom Ogilvy - 24 Jan 2006 16:04 GMT

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

Signature

Regards,
Tom Ogilvy

> 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
[quoted text clipped - 3 lines]
> appropriate worksheet? And from their can you then use a lookup
> function to lookup the appropriate data.

Reply to this Message

cvach - 24 Jan 2006 17:11 GMT

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

Reply to this Message

Tom Ogilvy - 24 Jan 2006 18:40 GMT

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.

Signature

Regards,
Tom Ogilvy

=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
[quoted text clipped - 7 lines]
>
> Any ideas?

Reply to this Message