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
ADVERTISEMENT
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.
Code:
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
View Related
May 4, 2013
Create a macro button in 'Spreadsheet 2' that searches 'Spreadsheet 1' for updated information specific to a certain criteria and adds it to a new row in 'Spreadsheet 2.'
There are three sheets
Prospects (where all original data is entered)
Actions -Bob (Bob's new Prospects are added to this sheet)
Actions -Frank (Frank's new Prospects are added to this sheet)
So in this example Row 6 in Prospects (Constituents, Rating, Manager and Solicitor would be added to ACTION - Bob's sheet on Row 5
and
Row 8 in Prospects (Constituents, its Rating, Manager and Solicitor would be added to ACTIONS - Frank sheet on Row 5
Here is the example spreadsheet - Prospects and Actions.xlsx
View 3 Replies
View Related
Mar 28, 2014
I have two worksheets. Sheet 1 has 2 columns, Column A the restaurant's name and Column B contains the review score. So sheet 1 is kinda like this:
Restaurant |Score
Ruby Tuesdays 80
TGIF 78
Outback 92
Sheet 2, Row 1 column B-E contain restraurant names (only on the top row, like field names).. i.e. I manually put the date in because typically the projected date is different from the actual review date.
-A----------- B ----------------C ------D-------- E-----
Date |Ruby Tuesdays|Olive Garden|TGIF|Ruths Chris|
I need the data from Sheet 1 Column B moved to sheet 2 in the next open row (i currently have data in row 1..the field names and down to row 35). This will be continuous so each time i need it to add the score as a new row in the correct field (restaurant name), IF the restaurant isnt listed, I want a new field named with the restaurant name and then place the score in the correct row and column. So, in the example I'd need Outback added.
View 9 Replies
View Related
Apr 29, 2013
I'm having a macro for search and all within the same file as folow:
Sheet"Search" has this vba
Code:
Option Explicit
Private Sub Worksheet_Activate()
[c3] = "Type your search here."
[c3].Select
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
[Code] ........
Sheet"Data" is my data (which is refer to above vba as Const SEARCH_SHEET = "data")
My needs here, i need to move Sheet"Data" to another file then how can I linke above vba to Sheet"Data" that moved to another file?
Infact, that file will be as addins file (.xlam) .
View 2 Replies
View Related
Apr 26, 2013
I would Need a macro which would Search a Keyword in the excel sheet and copies and pates the data in Cell "A2". for Example "Market" and then after the search it copies 12 rows upwards and 10 columns from the Cell that the word "Market" is placed. And then it copies 12 rows downwards and 10 columns from the Cell that the word "Market" is placed.
I have tried recording the same but it does not work if the Word "Market" is placed in different cell value.
View 2 Replies
View Related
Jan 19, 2014
Trying to grasp the concept of using these 3 functions to search for and return values from a data sheet.
The attached spread sheet has performance data for a group of employees.
What I need to do is find a particular employee then return a value for one of the category's.
For instance, I need to find "10TE03 ANGIE HOLLIS" Parts Usage on color or cell C10 in the attached sample.
Sometimes new category's are added to column A adding to the number of rows so a simple offset is not reliable.
Once I get that working, I then need to use a named range to total and average different data points for groups of employees by teams.
Maybe Offset-Index-Match is not even the way to go here?
View 7 Replies
View Related
Nov 12, 2009
I'm trying to find a way to search a second sheet in a workbook for specific criteria outlined in a first sheet (in my attached example, from A3 downwards within the 'list of search criteria' sheet), and then to copy any secondary data found against a successful search match to the original sheet, transposed against its corresponding matched search term.
As you can see in the example, the search term 'bindi' (A4 in the 'list of search criteria' sheet) appears in the 'data' sheet 3 times - the secondary data for these occurences ('feathery', 'Fibonacci', 'glassy') is copied to the 'bindi' row on the first sheet and is offset with each copy to produce a transposed-esque effect of copy and paste.
If it's any help, there are a maximum of 9 matches for a single search term in the real document.
Thanks in advance for your help... I tried to adapt a previous solution given to me for a similar question but failed miserably. I bow humbly to your expertise!
View 9 Replies
View Related
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
Mar 19, 2014
Table 1: in week 6, there is 1 unique client, in week 8 there are 2 unique clients,...
ClientsPMCWeek
AAAMoscowW6
AAAMoscowW8
AAAMoscowW8
BBBMoscowW8
BBBMoscowW9
CCCMoscwW9
DDDSTPW9
I want to create the following (not with pivot-table)
W6W7W8W9
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
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 /"
CODE:
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
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
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
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
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.
Example:
Code: Labels:
John FGA
John FGM
Joe FGA
Joe OREB
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
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.
*********************
Questions:
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
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
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
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
Feb 11, 2014
I am doing a spread sheet for participation in a class. What I want is for whenever a student is absent, i.e. has a 0 in their point box for the day, that cell turns red. I have tried to make the .find method work but it has been uncooperative and so far and I can't seem to get it to even run. This is what I have so far:
HTML Code:
Private Sub For_Loop_Click()
Set v = .Find(0, LookIn:=xlValues)
For Each v In [B6:B46]
Do
If v.Value = 0 Then v.Interior.ColorIndext = 3
Set c = .FindNext(c)
End If
End With
End Sub
View 2 Replies
View Related
Sep 14, 2009
Using the search macro code below, could someone please help to add in more codes what I'm currently using, and also where to insert it. The Search function works well for what I need and it helps me to locate data. When using the search function somehow it search all sheets within the workbook but I only want it to search an array of sheets when using this macro that is needed to complete the task for what I'm after.
Macro
Public Sub FindText()
'Run from standard module, like: Module1.
Dim ws As Worksheet, Found As Range, rngNm As String
Dim myText As String, FirstAddress As String, thisLoc As String
Dim AddressStr As String, foundNum As Integer
myText = InputBox("Enter the text that you want to search for:", "Start Search!")
If myText = "" Then Exit Sub...................
View 9 Replies
View Related
Jun 23, 2009
I am looking for a formula that searches a range of data and then returns the sum of the numeric values in the adjacent cells. I have a calendar style spreadsheet with 2 columns for each day. In the first column it has a list of names and the second column a number.
I would like a cell to return the sum of the numeric values next to each name. All the names are entered into the columns randomly and are unorganised. The same name will appear multiple times in the range and have different numbers next to it each time. I am currently using (for example) =COUNTIF(H6:AL14,"bob") to tally up the number of "bobs" but really what i want to do is find a formula that in plain english does:
Search a range of columns and where cell = "bob" take the value of the adjacent cell (to the right) and total them up!
i have attached an image - i hope it makes sense what i'm trying to do - i would like Q3 to total up the numbers to the right of all "bobs" across all the days - so it should return 42
View 2 Replies
View Related
May 14, 2014
What I have is a sheet that is copied periodically from some source sheet, and on this sheet is a table. This sheet is called "Onsite Checklist Template" and it's table is titled "Checklist". I also have another sheet called "Loggers and Initial Notes" which has a tabled titled "Record", and then finally a title sheet call "Proj Details".
To clear this intro up - The sheets, in their order, is: "Proj Details", "Loggers and Initial Notes", "Onsite Checklist Template". The tables: "Record" on "Loggers..." and "Checklist" on "Onsite..."
When the user wants to make a new site visit, he/she fills in the requested date and then selects a button on "Proj...". When this button is selected, it copies the table data on "Record" and puts it on "Checklist", then inserts a new worksheet tab, always in the 3rd position (the title is based on the site visit date in which the user entered), which is a copy of "Onsite...". Now we have another sheet with a table called "Checklist1", and upon another new site visit, there will be another worksheet with "Checklist2", and so on.
On the "Onsite..." worksheet, there is a button on it which also gets copied with the worksheet so that every new worksheet has this copied "Checklist ???" and this button. I'm looking for a macro that, when the button is selected, will bounce the active sheet's table "Checklist ???" off of "Record" and make changes as needed.
"Checklist ???" data range is B11:M20 (the header is on row 10); "Record" data range is B29:Q78 (the header is on row 28); Column headers are titled the same, just that "Record" has 4 extra columns, 3 in the middle and 1 on the end. "Checklist ???" columns 1-12 to "Record" columns 1-7, 10-12, 14-15. The search criteria is the 4th column in both tables ("Trk #").
I need the macro to do the following:If it finds a match, then update "Record" as needed with data from "Checklist ???", changing whatever cell is different in the row that contains the matching "Trk #", so long as the cell on "Checklist ???" is populated (i.e, if a cell on the target row of "Record" has a value, but it's blank on "Checklist ???", then "Record" wins; if it's blank on "Record", but populated on "Checklist ???", the Checklist wins. If both populated but different, then Checklist wins.If a "Trk #" exists on "Checklist ???" but is not on "Record" then add the line to "Record" (the 1st empty row, table size remains)I see no need for any "delete" at this time.
My concerns: The last column on record (column #16) is the filtering column for the worksheet copy event and needs to be left alone (it's formulated to produce a "Yes" or "No")I would like the ability to adjust table sizes if needed without modifying the macroThe table rows on "Checklist ???" will not be changed, deleted, or altered in any way by the macro.
View 2 Replies
View Related
Jun 18, 2013
Here is an example of my spreadsheet: Excel example.xlsx
I would like to get all the bid/ask quotes for the different currency pairs in sheet 2 into sheet 1 sorted by the right date and time.
I need a formula which recognize the time + the currency from sheet 1 and search for it in sheet 2 then brings the right bid/ask quote back into sheet 1.
View 2 Replies
View Related
Jun 28, 2006
I have included the table. What I want to do is in a user form pick a county ie Devon, Then by clicking "go" all entries that contain "Devon" are then copied to a " sheet 2" I don't want to use the Ctrl-F
View 2 Replies
View Related
Jan 14, 2013
I've got 3 problems I've been trying to solve for the last 7 hours, I think they are all relatively simple to those who know what they are doing but I've exhausted my excel for dummies and every formula I have taken from this siteand tried to adapt. Obviously I'll post each problem on a different post, so here's problem one. The sample is attached. It will consist of about 35 sheets, each documenting the hire of a Motability scooter or wheelchair.
On each sheet column P will identify any review dates. What I would like is: On the Master page in Column C next to the relevant sheet name is for it to display any 'Review' that is flagged up, so that the user can have an overview. There will only ever be one 'Review' per sheet, I can only get the master page to show it by referencing it to one cell, I can't work out how to search the whole column.
View 4 Replies
View Related
Dec 9, 2011
I would like in sheet1 a button, so the number is typed in then press the button and it will take me to that number in sheet2.
I have tried a few macros but they bring up multiple numbers and didn't take me to cell.
View 9 Replies
View Related
Sep 21, 2013
I have a WB with multiple sheets. I have a Feed sheet that automatically pulls data from the web. I have a Scores sheet that currently has all head to head matchups each week for an entire season. So I want to check the cells on the Feed! sheet to find a partial match and if partial match exists then copy cell that contents score from feed sheet to the score sheet next to the appropriate teams name. Currently I have to manually enter all scores each week for the rest of my WB to update.
I need to look at Cell A3 (on the Feed sheet) which has "New York Jets" then search for a partial/similar match on the score Sheet (which is NY JETS, in this case). NY JETS could be in column B (rangeB2:B257) OR column D (rangeD2:D257) BUT I need to search by row, not column, then once a match is found check the cell to the immediate right and only if the cell is blank copy data from the (!feed) sheet to that blank cell on the (!scores) sheet
This is what happens if working right =
look at cell A3 on the (!feed) sheet = "New York Jets" then search (!scores) sheet Column B and Column D by row for a partial match, finds "NY JETS" as match in cell D8, if cell E8 is blank then copies cell H3 from (!feed) sheet, and pastes to cell E8 on the (!scores) sheet, if cell is not blank continues search until 1st blank cell to the right of matching cell is found (as there will be mutiple matching cells with blank cells to the right but I am only interested in the 1st blank cell found, once found and data copied the process is done and then starts over with cell A4)
Look at cell A4 (!feed)= "New England Patriots" then search (!scores) sheet Column B and Column D by row, finds "NEW ENGLAND" as match in cell B8, if cell C8 is blank then copies cell H4 (!feed) sheet, and paste to cell C8 (!scores)
Once this is done it moves on to the next cell in the next row on the (!feed) sheet, A5, to find a partial match for the data in that cell. I need to continue the search for each cell A3 to A74, and if no match is found to move on to the next cell A6... (based on the way the data is pulled in from the web there are some blank cells as well as some cells that say Game Final, this data won't be on the scores sheets in column B or D).
View 1 Replies
View Related
May 7, 2006
example of a database user form that will allow me to list records in a sheet as well as search for records in a sheet. I know excel has a built in feature for this but it is menu driven and I need something that is button driven and will allow me to resize the form layout. I was not able to figure out how to do that with the built in form.
View 7 Replies
View Related
Oct 6, 2009
I have an excel spreedsheet that has columns of numbers. ex.
SKU - DESC - SKU - DESC - SKU - DESC
I enter sku's and information about them each week all on the same sheet. Each group sku/desc is one week. I need something I can put in the conditional formating.. a formula that will check the columns previous and tell me if a number appears in any column before the one I'm typing in.
ex.
6413 - B/O 2145 - B/O
4434 - B/O 6413 - B/O
4456 - B/O 4356 - B/O
4567 - B/O 4967 - B/O
76677 - B/O 76477 - B/O
I would like second 6413 to highlight to show me it's a repeat!!
View 3 Replies
View Related
