Search Box Searches Multiple Worksheets

Jan 9, 2013

I love Excel and love learning new things to do in it to create some amazing number-crunching techniques. I have created a workbook with multiple worksheets [one for every month in the year]. I searched this amazing forum and succesfully implemented the search box macro to find and copy the found data on a separate worksheet.

My question is that the macro I found in the this post Creating a Search Box in Excel only searches for the data in one worksheet. How do I tell it to search for the inputted data in all worksheets?

Here is the first part of the macro. I was trying to fiddle around with the strDataShtNm but to no avail.


Sub myFind()
'Standard module code, like: Module1.
'Find my data and list found rows in report!
Dim rngData As Object
Dim strDataShtNm$, strReportShtNm$, strMySearch$, strMyCell$
Dim lngLstDatCol&, lngLstDatRow&, lngReportLstRow&, lngMyFoundCnt&

On Error GoTo myEnd

strDataShtNm = "Sheet2" 'This is the name of the sheet that has the data!
strReportShtNm = "Sheet1" 'This is the name of the report to sheet!

View 2 Replies


Search Box That Searches And Retrieves Data From Different Sheet

Jan 5, 2009

I've got a spreadsheet (sheet1) that has details of vehicles helpline numbers.

I want to create search box on a seperate spreadsheet, where the user enters a reg number, and it is searched for in sheet1. The row containing the result is then shown under the search box. Is there anyway to do this?

Also, I'd like to lock sheet1 so people dont see anything other than their search criteria. (ideally, I'd like sheet1 to be in a totally separate workbook).

View 9 Replies View Related

How To Create Search Macro Button That Allows To Search In Multiple Worksheets In One Workbook

Oct 11, 2011

Im am trying to create a search marco button that allows me to search in multiple worksheets in one work book. I came across this CODE the first part of it works. It pops open user input box and ask for the word that i would like to search but the this error message pops up Runtime error1004 Method 'range" of object'_Global'failed and i dont know what to do

Private Sub SearchButton_Click()
SearchString = InputBox("Enter Search String", "Search")
If SearchString = "" Then Exit Sub
For Each c In Range(myRange)
If InStr(LCase(CStr(c)), LCase(SearchString)) Then


View 1 Replies View Related

Search Multiple Worksheets Against List Of Non-Exact Search Criteria?

Nov 2, 2009

I have a workbook with many many sheets in it. The first sheet contains a single column with about 10,000 different values. I'd like to use each of these as search criteria against ALL data in the other sheets (of which there are a good 50 or so). If matches are found (they don't have to be exact case), then I'd like two things to happen:

1. The rows containing the matched search criteria in the first sheet are highlighted.

2. In the cells adjacent to the search criteria in the first sheet, hyperlinks to the matched data are created and named after the sheet upon which this matched data appears.

I've attached a sample file to this post with ideal sample 'answers' to queries made of the first 2 terms.

View 3 Replies View Related

VBA UserForm That Searches And Reads Open Worksheets?

Dec 24, 2013

Develop one system that can search and make changes on the user form. I already find the solution on my first worksheet(USER PROFILE) but when i used the same coding on my second worksheet(INVENTORY&RET), it doesnt work. Here is the example of my INVENTORY&RET worksheet example.xlsm

Hit MAKE CHANGE button to see the problem.

View 2 Replies View Related

Using Multiple Match Searches In Index

Jan 10, 2014

To summarize - Columns B through E are current MLB Players who I am trying to compare with the data in Columns G though J. My goal is to both use index and match to find the following in Column L: A comparable player name using the criteria of having an exact match for both position and age, but the closest approximation match between Columns E and J.

I have deleted a significant amount of player data to be able to upload the document on this site.

I am trying to find a way to combined exact and approximate matches in one single formula, but have been unsuccessful thus far.

View 10 Replies View Related

Multiple Text Searches And Counts

Jan 21, 2009

I have data created by a program where it gives me a column "Code" with different text categories (in this case names of people). One person appears multiple times in this column as it track each time they register a statistic for the sporting event. The statistics are text fields registered under the column Labels

So I have two columns titled "Codes" and "Labels"

I need to extrapolate from this data a count of times each statistic happens for a specific player.

Code: Labels:
John FGA
John FGM
Zach TO

What I need is broken down by player with counts of each category FGA, FGM, OREB ect specific for each player creating a box score for the event.

View 9 Replies View Related

Not Sure To Use Dsum Or Vlookup For Multiple Searches

Feb 27, 2009

I would like to ask recommendations whether to use DSum or Vlookup, in order to do multiple search.

We receive a flat file on a daily basis, which contains four (4) columns. They are Group, Team, Taskname, and Number_of_Employees. The first three columns have text properties and the last one has number property. We copy this sheet into our workbook. There are more than 1,000 rows. I name this tab, "TotalWorkersbyTask".

Each group (first column) has its own tab. We try our best to name each tab to match the name of the first column.

We would like to find the unique value, in order to put the correct Number_Of_Employees into the correct column on each sheet. However, the relationship per column is many to many, which makes it more difficult.

For example,

Group,Team, TaskName, Number_of_Employees

12GR, Robert, Analyze raw data, 2.5
12OV, Tim, Analyze raw data, 1.25
12OV, Bob, Prepare reports, .85
16CJ, Jim, Prepare reports, .50
1008, Mary, Analyze raw data, .25

Each tab, such as 12GR, will have the task name on the line "Analyze raw Data" which matches the information "TotalWorkersbyTask" tab.

The problem is, if I do a Vlookup for Taskname only, only the first value of 2.5 will return in to the Number_of_Employees column.

For the tab of group 12GR, the logic supposes to be -> if the group is 12GR, and the name of the task equal to the one appeared in "TotalWorkersbyTask", return me the value of 2.5 in the specific row. With the same logic, on the tab 12OV, the same task name should return me the value of 1.25 as well.

As you see from the example, line 2 and line 3 are the tasks in the same tab, from the flat file received.

Then, this formula can be copied and populated into other rows and other sheets as well.


1. Is there anyway to write the formula to gather the required information? My thought was using the Vlookup. However, it can see only one column.

2. Do I need to rename all the tabs to match the name of the first column received from flat file in order to make it more efficient? (In the present, there are some tabs that match the name, such as 12OV, and some tabs are something like ->Engineering, Research, etc.)

View 9 Replies View Related

Multiple File Searches In One Macro

Apr 11, 2007

Is it possible to do multiple file searches in one macro? Or does the data get stored in such a way that they'd conflict? First I want to search a folder for other folders. Then I want to search each folder for a certain type of file and then run a macro that does a whole bunch of things to each file. Including saving the file as a different format in a folder with a similar but different name. Currently I have everything written except the loop that will go through each folder. I was kind of hoping to use a 2nd file search to do so.

View 5 Replies View Related

Search Function Across Multiple Worksheets?

Apr 24, 2014

I have 6 worksheets with data. Column C of every worksheet is filled with unique Tracking Numbers.

I want to create a 7th worksheet with a search function. Users will be able to type in a Tracking Number (on cell A2), and information associated with that Tracking Number (from Columns A, D, E, F and G of worksheets 1-6) will show up beneath cell A2 on cells A4, B4, C4, D4, and E4.

View 3 Replies View Related

Search Multiple Worksheets For Several Keywords

May 11, 2009

I am trying to set up a worksheet which takes keywords from several cells (user populates these cells) and searches all worksheets for these keywords.

for example, i have C1:G1 set up as possible search string entry cells where the user types their text. I need a function which takes the data in whichever of these cells have been filled in (i.e. it may only be in 1 cell, 2 cells or all 5) and searches each worksheet for all cells containing ALL keywords. (as it finds each one, i will highlight the cell, but that part of the code is obviously very easy )

Currently i have this (note, i havent yet figured out how to but the k1 to k5 variables into the search string yet which is why they are set but not used):
At the moment, its not ever looking past worksheet 1 - can anyone see why? and then how to i get it to look for all entered keywords?

View 12 Replies View Related

Search A Workbook With Multiple Worksheets

Mar 28, 2008

Need MACRO to search a workbook with multiple worksheets?

View 9 Replies View Related

Using Power Query To Do Multiple Searches On Website?

Jul 10, 2014

I've been trying to get power query to search a list of parcel numbers on an assessor site: [URL]. The problem I'm getting, every time you put in a different parcel number, you get the same URL for the results. I want to extract just a few pieces of information for each parcel search, but I can't initiate a search through excel. I'm thinking the problem is in the Data source settings. How can just get it to show me the assessors page results for a single parcel search in excel?

View 5 Replies View Related

Search Copy And Paste Across Multiple Worksheets?

Oct 12, 2011

I have a workbook with 6 worksheets in, 5 worksheets contain data whilst the 6th I will use as a search worksheet. The 5 data worksheets contain columns A to J which have text entries in.

What I would like to click a button on the search worksheet, lets call it worksheet 1, and for an inputbox to allow the user to enter a text search. The macro would then search for this text in columns A to J and all rows (or rows with data in) on the 5 data worksheets, and if found, copy the entire contents of those rows where the text is found and paste them into worksheet 1, the search sheet.

I have done a forum search and found a few examples of this type of search but not across multiple worksheets, also I found this code that does search across multiple sheets but does not copy and paste:

Sub Find_Data()
Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer


View 5 Replies View Related

Data Colletion, Search Multiple Worksheets

Dec 18, 2006

There is a workbook that has several worksheets.

I would like to collect a specific information to the 1st page of the workbook.

It should do the following, when i enter a productcode to a cell in the 1st page(data collection), it instantly searches the other worksheets for a similar productcode, if it finds a match then it should bring 3 different cells on the same row to the 1st page.

View 9 Replies View Related

Search Multiple Worksheets For Text String Then Sum Corresponding Values?

Sep 24, 2010

I have a workbook that contains 18 sheets of data. These worksheets are named according to their respective client names (AAA Shine, Fern Barn, Oracle, etc. for example). On each of these sheets, is a column (Column B), that has a salesperson's name in it (Fred, Joe, Ted, Anne... there are about 10 names total). On each sheet, there are corresponding values for the revenue from that account for each month of the year. Those values appear in Column L (Jan), M(Feb), N(Mar),... W(Dec).

What I would like to have is formula that will look at each sheet, find all the instances of Fred for January, sum them, and report them in a single cell on a different sheet I have called "Summary". The end result will be a total revenue from all customers, for each sales person, by month, in one summary sheet.

View 9 Replies View Related

Search/Find Values From Multiple Tables/Worksheets

Aug 21, 2006

As in attached file, I have 5 tables:
Table 1: matrix that contains Weight data in each cell
Table 2: matrix that contains Code data in each cell
Table 3: Relationship between Code and Level data
Table 4: Table with relationship between Weight data, Code data and Level data
Table 5: I want to populate each cell in Table 5 matrix from getting values from Table 4 by looking up the right value from Table 5 based on :
1) Weight data from corresponding cell in Table 1
2) Code data from corresponding cell in Table 2
3) Code and Level relationship.

for e.g. to get value for Table 5, Worksheet cell B140 ( intersection of A1-A1),
1) corresponding weight is 1.17 as in Table 1 (Weight data) (Worksheet cell B3)
2) corresponding Code is 6 as in Table 2 (Code data) (Worksheet cell B35)
3) Code 6 corresponds to Level 2 (as seen in Table 3)(Worksheet cell A74)

so, looking up the 3 values from Table 4 i.e. Level 2, Code 6, Weight 1.17, one gets cell AR98 with corresponding value. therefore, final value in Table 5, Worksheet cell B140 (intersection of A1-A1) will be equal to $4.00

I have been trying to solve this using Vlookup, Hlookup, Index/Match but am getting confused. Also, not too sure if this can be done in 1 step. New to these excel and these functions

View 2 Replies View Related

Copying Data From Multiple Worksheets To Multiple Worksheets In Another Workbook VBA

May 14, 2012

I have 2 nearly identical workbooks and I need to update historical data from the old workbook into the newer one.

My current Coding Snippets that I want to use look like the following:

Sub UpdateWorkbook()
Dim ws As Worksheet
Dim r1 As String
Dim r2 As String
Dim r3 As String
Dim r4 As String
Dim r5 As String
Dim r6 As String


Now, this code isn't working I suspect because the Copy and PasteSpecial Functions don't work the way I wish to.

View 4 Replies View Related

Using The =IF(ISNUMBER(SEARCH Function To Search Multiple Cells)

Aug 21, 2009

I' having trouble using the =IF(ISNUMBER(SEARCH formula to search multiple cells.

I can get it to work to search one cell (as below):

=IF(ISNUMBER(SEARCH("Same",G10)),"No Change",'Aug 09 Matrix'!F10)

(In this case the respone is No Change as Cell G10 contains "Same")

However cannot get it to work for several cells. I have pasted below and highlighted the function I would like it to perform:

=IF(ISNUMBER(SEARCH("Same",G10:R10)),"No Change",'Aug 09 Matrix'!F10)

Essentially the other cells in that row (G10:R10) all contain "Same", however for some reason it is identifying this as FALSE and putting in the data from the 'Aug 09 Matrix' sheet.

View 9 Replies View Related

How To Ask Formula To Search For Multiple Values Within One Search

Mar 4, 2014

I need one formula to copy and paste the checkcard data changing it from this "CHECKCARD XXXXXX5623" to "CHECKCARD 5623", but I need one formula to look for multiple known checkcard numbers. I will have more then one typically in the bank download so I need to search and copy at least two known numbers as shown below.

I am providing the formula that I am using right now, "=IF(ISNUMBER(SEARCH("5623",B3)),RIGHT(B3,41),"")" but it only works for one checkcard number? Also I don't really need to capture the DEBIT or CREDIT that will always be at the end, it would be redundant. If it's easier to write the formula including that data that is no problem. What I want to copy and move into an adjacent cell is in red.

Here is the source data:

View 4 Replies View Related

Using Search Across Different Worksheets

Dec 5, 2007

I have 3 worksheets in my workbook, these are listings of people in offices, their address and phone numbers. Unfortunatly the acurate data is split between 2 worksheets and I'm trying to gather it into one.

First worksheet has columns: First Name, Last Name & Phone number

Second worksheet: Address, Full name (sometimes more than one people separated by commas)

What I want to do:
-Import the First, Last name and Phone from worksheet 1 to the new one. Done!
-(Now the complicated part) For each row in the new worksheet, gather the First and Last name together and run a search on the 2nd worksheet's Full Name column (the one that sometimes have more than one name separated by commas) and if a match is found, return the address information on the corresponding row to a specific cell.

Preferably I wanted to do this without the use of VBA as I'm an absolute beginner in the language.

A link to the test file I'm using: Link!

View 9 Replies View Related

Formula That Returns Value From The Same Row It Searches?

May 27, 2014

I need a formula that can return the value from a cell in the same row it searches. The row is unsorted and has blank cells. Seacchs row A12:X12 returns the text in C4 (data validation List) in A19 on a different sheet.

View 6 Replies View Related

Conditional Count With 2 Searches?

Mar 19, 2014

Table 1: in week 6, there is 1 unique client, in week 8 there are 2 unique clients,...


I want to create the following (not with pivot-table)

Nr Clients 1023

I tried with combinations SUMPRODUCT/COUNTIF/... The problem is that you must combine with W6/W7/W8...

View 1 Replies View Related

Using Find For Similar Searches

Mar 6, 2014

Problem is Findstring finds cells that contain "SS1" AND "SS1 EXT". These are separate grades.

I need Findstring to only find SS1. Using xlWhole doesn't work for me, as the cells also contain other data. For example: " SS1 48 /"


FindString = "SS1"
Findstring1 = "SS1 EXT"

With Sheets("Sheet1").Range(Rng4)
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _

[Code] ........

View 1 Replies View Related

Macro To Search All Worksheets

Jul 30, 2007

I've been writing a little macro to prompt a user with a form which lets them select 2 reports to open and also lets them select a month. One report contains financial data for the month and the other report contains all data for the year. So each month someone has to copy data from report 1 into report 2.

I have so far been able to:
-create form with error handling to open files
- select values from a pivot table in report 1
- search pivot table 1 (single sheet) and copy the Name and Total $ fields

what i haven't been able to do:
When i get the data from report 1 I switch to report 2. When i get to report to i need to search all the sheets in order to find a name with the original Name field from report 1 (I then have a HUGE case statement that will tell me if name = this and month = that then paste in cell $x$y). However, whenever i try to run my search across all sheets it will only find values on the sheet that is selected at the time.

I've attached my search code below, I would appreciate any help i can get as I've spent all day trying to resolve it with no luck

Function findinworkbook(TruncName)
Dim ws As Worksheet

On Error Resume Next

For Each ws In ThisWorkbook.Sheets
' Find Function
Cells.Find(What:=TruncName, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Comment.Text Text:="test" 'test to mark where the matched cells are
On Error Goto 0

End Function

View 5 Replies View Related

VLookup Function That Searches For Two Variables?

Dec 9, 2013

I need to figure out a vlookup function that searches for two variables and return an answer. such as the example attached;

vlookup -- 12253 & 605.34 (if these are matching then) 131001

View 5 Replies View Related

Create IF Function That Searches For Word No?

Jul 28, 2014

I am trying to create an IF function that searches for the word "No." I have created a table with monthly payments and if the payment is >= 0, it returns the word Yes. If the payment is

View 3 Replies View Related

Search And Match Data Using Two Worksheets

Apr 2, 2014

I'm trying to search & match data from two different spreadsheets. I will attach my workbook for reference.

The first worksheet is a list of all of my clients I have previously worked with and the second worksheet is a list using a set criteria. The criteria I am using is the UK postal code "AL10".

The clients address (Column B) will be used as a reference to match the address which is located on the AL10 worksheet which is also column B. If there is a direct match then a VLookup function will be performed to display something that can be easily referenced.

The problem I am having is that the address format is different on the clients worksheet then what it is on the AL10 worksheet. I have the feeling I will need to create a search function with multiple arrays but I have limited knowledge of how to do that.

There are some additional notes located in my workbook.

I know that two of the client addresses should match data located on the on AL10 worksheet and the other two shouldn't give a match at all as they don't exist. These are highlighted in yellow.

I have used the Find and replace function to do this but this is rather manual and slow and I would like the search feature to automate this process.

Attachment 308707

View 6 Replies View Related

Create Search Within Excel Worksheets

Jul 22, 2014

Attached is my excel worksheet I have created for a tracking/billing system for numerous schools. How I can create a search form?

For example, I want to find all of the kids in 5th grade that had to be referred throughout the whole document. Or I want to find a certain name to see if they have been paid but I do not know the location.


View 7 Replies View Related

Search Worksheets, Conditional Counting?

Mar 9, 2007

My workbook is made up of a number of worksheets, A master sheet, which creates new sheets for each work order I enter. Therefore, eventually I will end up with a large number of worksheets that I will have to sort through to find what I need. I put in a command button to allow me to search and hide all of the worksheets that do not match the the work order number I entered. However, if no sheets contain a matching number, then all sheets will be hidden, except for the master sheet. I want to add an exception that pops open a message box if no matches were found and stops the code. I was thinking of doing a conditional count to count all worksheets that match the criteria. If the count = 0, then the message box will open.

Private Sub SearchButton_Click()
Application. ScreenUpdating = False
Dim ws As Worksheet
Dim Search As String
Search = InputBox("Enter work order", "Search Archives", "")
For Each ws In ActiveWorkbook.Worksheets
If ws. Range("D4") <> Search And ws.Name <> "Master" Then
ws.Visible = xlSheetHidden
End If
Next ws
Application.ScreenUpdating = True
End Sub

View 2 Replies View Related

Copyrights 2005-15, All rights reserved