I am setting up a spreadsheet to keep track of phone numbers.



The workbook has worksheets named "A", "B", "C", "D" etc!



The first worksheet is named "Menu" and contains all the letters of the
alphabet with macros to link to the various worksheets, for example if I
click on "A" it will go to the worksheet named "A" that contains all company
names and phone numbers for companies that name starts with "A"



What I would like to do is set up a something on the "Menu" worksheet that
could search the workbook by the company name. For example if I was to type
a company name such as "AIB" it would return the number for "AIB".



Would this be possible and if so what would be the best way to go about it?



Thanks

Since I do not know your data structure, I make the following assumptions,
all columns A - Z are set up the same, for my scenario, column A on each tab
is company name, column B is phone number, and A3 is the cell on the menu tab
where you are typing the company name:

=VLOOKUP(A3,INDIRECT(LEFT(A3,1)&"!A1:B100"),2,FALS E)

That being said, you will have difficulties if the person does not type in
the company name exactly, such as abbreviations, periods, commas, etc.

I have 2 suggestions:
If you want to keep the company names separated by tabs like you have now,
i.e.: tabs A-Z along with a menu tab, then you could have the person select a
letter A-Z, and then choose from a drop down list. Again, assuming data
structure is same from tabs A-Z, and A3 is now where the person would just
select a letter A-Z.
In B3, go to Data--Validation, under allow, choose List, and under source,
type the following: =INDIRECT(A3&"!a2:a100"). Ensure Ignore Blanks is
checked, and increase the 100 as needed. Then in cell C3, type the following
formula:
=VLOOKUP(B3,INDIRECT(A3&"!A1:B100"),2,FALSE)
This method will allow the user to select the letter for the corresponding
company, and then use a drop down list to ensure exact matching.

My second suggestion is to put all the companies on the same tab if you
don't have an overly large list. You can sort the companies when you need to
on your company tab, and you wouldn't have the extra step of choosing a
letter, so:
in cell A3, go to Data--Validation, choose List, and type:
=INDIRECT("Company!A2:A100")
In cell B3, you would have the vlookup as suggested for cell C3 in my first
suggestion.

NOTE: The Indirect is still necessary to pull a list from a separate sheet.
--
John C


"Eamon" wrote:

I am setting up a spreadsheet to keep track of phone numbers.



The workbook has worksheets named "A", "B", "C", "D" etc!



The first worksheet is named "Menu" and contains all the letters of the
alphabet with macros to link to the various worksheets, for example if I
click on "A" it will go to the worksheet named "A" that contains all company
names and phone numbers for companies that name starts with "A"



What I would like to do is set up a something on the "Menu" worksheet that
could search the workbook by the company name. For example if I was to type
a company name such as "AIB" it would return the number for "AIB".



Would this be possible and if so what would be the best way to go about it?



Thanks