need help to create a workbook search

[axidburn]

hello. im a newbie here, and in need of help. i am not a pro on excel, however, know enough to get around. i have excel 2010 and use it to enter my expenses in my workbook. each workbook has an individual worksheet for each of my clients, upto 50 worksheet in each workbook. each worksheet is numbered exclusively for each client.

question: how can i write a formula or put in a lookup so i only enter my clients number to pull up the worksheet instead of searching the workbook for a specific worksheet.

thanks!

[mrice]

Suggest using a macro like this.

Please Login or Register  to view this content.

Open up the VBA editor by hitting ALT F11

Insert a new module by hitting Insert - Module

Paste the macro into the empty sheet

Hit ALT F11 to get back to the worksheet.

Run the macro by going to tools-macro in Excel 2003 or the view ribbon in Excel 2007.

[axidburn]

EXCELLENT! worked like charm.
please, now how can i have a cell with this formula so that i ONLY have to enter the worksheet name to find it.

[mrice]

Paste this code into the approproriate sheet tab in the VBA editor (Sheet1 for example).

Please Login or Register  to view this content.

Adapt the above to contain the address of the cell in which you wnat to enter the sheet name if it is not $A$1.

[axidburn]

hello all. thanks for the formula. has been working great. but there is a bug. i have over 6 workbooks with 50 worksheets in each workbook. the macro search is excellent. however, when i run the macro, opens up another workbook, the original book installed the formula. is there a fix? and also, how do i set a CNRL R button for the macro.

thanks guys!