Solved

Excel 2007 - Need a way to find a customer quickly

Posted on 2010-11-11
12
487 Views
Last Modified: 2012-08-14
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
Comment
Question by:Morya1
  • 4
  • 3
  • 2
  • +2
12 Comments
 
LVL 3

Expert Comment

by:governor_arnold
ID: 34116617
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
 
LVL 10

Expert Comment

by:Mez4343
ID: 34116787
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
 
LVL 10

Expert Comment

by:Mez4343
ID: 34116797
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
 
LVL 50

Accepted Solution

by:
Dave Brett earned 250 total points
ID: 34116799
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
 

Author Comment

by:Morya1
ID: 34117834
Mez4343: don't see any smiley face - see attachment
 no smiley face
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34117907
Do you know how to use the find feature?
Hit Ctrl+F and type the guy's name.
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34117912
Oh, expand the 'Options' thing and make it search 'Within' the entire Workbook, not just the sheet.
0
 
LVL 10

Expert Comment

by:Mez4343
ID: 34121243
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
 

Author Comment

by:Morya1
ID: 34121557
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
 

Author Comment

by:Morya1
ID: 34125013
brettdg, have you found that flexfind causes an error all too often? see attached. flex find error
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 34125522
> 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
 

Author Closing Comment

by:Morya1
ID: 34142729
It gives me errors, but I like what it does.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now