Using Excel, how do you search by Tab names?

Question

I've just been sent an excel spreadsheet or workbook if you like... There are hundereds of tabs each referering to different places and when clicked on of course different statistics for those places are displayed. It's taking me ages to find the tabs i need as none of them are in order. Is there a way of seaching for a specific tab name?

Answer 1

I guess only using VBScript Macro...

Answer 2

I don't think you can include a tab name in a search within a workbook.

But there is no reason why you can't put a tab name into a worksheet and find that!

To avoid finding a heap of other stuff in the workbook which matches the tab name, reduce the search options to only comments. Go to the first sheet, go to cell A1, insert a comment, type the tab name as the text in the comment, close the comment. Cell A1 now has a comment the same as the tab. Do this for all the sheets.
Click the find icon, click options,
click "within" and select entire workbook.
click "lookin" and select comments. This will avoid finding matches within any sheet.
click "find next"
hey presto!
TIP = just to the left of the sheet name tabs are the four direction buttonsfor tab scrolling: right click on one of the buttons and a window list of tabs appears - sometimes it is easier to navigate with the window if the sheets are arranged into some sort of order.

Answer 3

I like portions of two of the answers. Right clicking the arrows in the bottom right hand corner works really well. So would pressing the F5 button to use the GoTo dialog box. But there are still two more methods that you could use to find your data.

Press Ctrl + F. This invokes the Find and Replace dialog box to find text. By default, it searches for just text in the active worksheet, but it can be set to search the entire workbook. If you click the Options button, you can change Within: from Sheet to Workbook. That allows you to search the cell contents of everything in the entire workbook versus just the active sheet. It won't search for tab names but if you know some text or a value that should be in that sheet it will find it for you quickly.

The other thing I would recommend is creating some named ranges in your workbook, especially for the data you need to find. Then if you ever need to find it later all you have to do is type or select the name in the Name Box in the top left corner of Excel.

A last thing you might consider doing is color coding the tab if it is an important one afterwards. Right click the tab and select tab color. A bright red tab among a bunch of grey tabs tends to stand out.

Answer 4

All you need to do is:

F5 (Edit-Go To)
enter !A1

So if you want to go to the tab "Saudi Arabia", enter in the go to box:
'Saudi Arabia'!A1

You only need the quotes if there is a space in the name.

Good luck.
.