Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 499
  • Last Modified:

Excel 2007 - Need a way to find a customer quickly

Hello future point winner!

I have a workbook that has about 50 tabs or so. Each tab represents a customer sheet, and each sheet has customer ID, name, etc. I am needing a way to quickly find Customer John Smith without having to scroll through the tabs or right clicking on the scrolling arrow (which pops up a list).

Is there a way I can find a customer or a value (like the name or ID) within the sheet through a code, formula or some shortcut I don't know about? If so, could you provide or show me the way?
0
Morya1
Asked:
Morya1
  • 4
  • 3
  • 2
  • +2
1 Solution
 
governor_arnoldCommented:
I think the easiest way to find something is to use the built in Find option using ctrl-F.

When the find and replace window pops up, hit the options button and select Workbook instead of Sheet and it will search all of your tabs and bring you to the right cell.
0
 
Mez4343Commented:
If you wanted to extract the names to lets say a new sheet so you can sort,print, etc. See attached Excel sheet. It contains a VBA macro that will do that. Just click the smiley face icon in upper left corner.
 PrintNames.xlsm
0
 
Mez4343Commented:
If you want to learn about macros, you might need to enable the Developer features in Excel.
http://msdn.microsoft.com/en-us/library/bb608625.aspx
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
DaveCommented:
JK has a free addin that enhances the native Excel find functionality

FlexFind can be downloaded from http://www.jkp-ads.com/officemarketplaceff-en.asp

Cheers

Dave
0
 
Morya1Author Commented:
Mez4343: don't see any smiley face - see attachment
 no smiley face
0
 
TommySzalapskiCommented:
Do you know how to use the find feature?
Hit Ctrl+F and type the guy's name.
0
 
TommySzalapskiCommented:
Oh, expand the 'Options' thing and make it search 'Within' the entire Workbook, not just the sheet.
0
 
Mez4343Commented:
Mory, Sry it is the new Macro security thing in Excel 2007. He si the macro code so you can create your own without worrying about security issues.

Sub ExtractNames()
'Add A Sheet After The Last Sheet
Sheets.Add After:=Sheets(Sheets.Count)
'Initialize Row Counter
sRow = 1
'Loop Through All Sheets Except The Last One
For Shts = 1 To Sheets.Count - 1
'Copy A1 or where ever you have the Name from the Cell or cell range
Sheets(Shts).Range("A1:A1").Copy
'Paste Each Sheet's Name Into The Last Sheet Below Previous Sheet's Data
Sheets(Sheets.Count).Cells(sRow, 1).PasteSpecial
'Determine Next Empty Row In The Last Sheet So We Know Where To Paste Next
sRow = Sheets(Sheets.Count).Range("A" & Rows.Count).End(xlUp).Row + 1
'Loop
Next
End Sub
0
 
Morya1Author Commented:
I haven't tried out your code Mez, thank you, and I did install the Flexfind brettdj - and it works - but is there, and I know I'm changing the initial question, is there a code, add-in or whatever that will search through all workbooks, open or closed to find a specific value. Just found that is necessary because I'm trying to find a name right now with flexfind, and I know it's in some workbook, I just don't know which one, and it's not coming up.
0
 
Morya1Author Commented:
brettdg, have you found that flexfind causes an error all too often? see attached. flex find error
0
 
DaveCommented:
> is there a code, add-in or whatever that will search through all workbooks, open or closed to find a specific value

For this youu will need to use an indexer such as Google Desktop Search or Verity Enterprise Desktop Search

There are a range of free tools at http://www.freedownloadscenter.com/Best/file-grep-tool.html

The grep tools give you more flexibility in specifying patterns for your seach (ie find "fred" where "martha" is not the next word etc)

Cheers

Dave
0
 
Morya1Author Commented:
It gives me errors, but I like what it does.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now