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


ADVERTISEMENT

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 Multiple Cell Values On Another Worksheet

May 20, 2008

I have three parameters that users on my spreadsheet will be using. They will be searching using the 'Brand', 'MPAN' and 'Tariff' fields.I would like to have drop down menu's for then to select the correct search option. I then have another sheet within the same workbook with all the data in it with columns headed of Brand', 'MPAN' and 'Tariff'. It has to be an excat match using those three parameters though. I would like the parameters the user selects on the first sheet to be searched and found on the second sheet, with the figures in the cells next to the search result getting fed back into a set cells back on the first page.

View 9 Replies View Related

VLOOKUP Find Values From Multiple Worksheets

May 22, 2009

I want to find sum of all 4 returned values (Sales!BL5, Sales!BM5, Grants!X5, Deferred Pymts!X5

Column References:

Cell AD2 (AMOUNT TOTAL PYMTS)
Cell AC2 (TOTAL NO. OF ORDERS)
Cell Range B5:B4833 on multiple pages (CUSTOMER NAME)
Cell Range AJ5:AJ4833 on multiple pages (CUSTOMER NAME)

Formula:

In Cell AD2 of CustomerData! Sheet:

If AC2 is not blank, then
look for match between A2 and Sales!B5:B4833, return the value in Sales!BL5, and
look for match between A2 and Sales!AJ5:AJ4833, return the value in Sales!BM5, and
look for match between A2 and Grants!B5:B4833, return the value Grants!X5, and
look for match between A2 and Deferred Pymts!B5:B4833, return the value Deferred Pymts!X5

Hope this does not confuse, but if John Doe is listed in AC2 I want to find the sum of his total payments if his name is listed as a customer in Sheets Sales, Grants, and Deferred Pymts. The Sales! sheet is divided into AM and PM pricing which explains why Sales! has Column B and Column AJ for Customer Names.

View 13 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

[Code]....

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

Pivot Tables Multiple Worksheets

Nov 17, 2009

Background: I know ZERO about VBA and I am a Vlookup & Pivot Table noob, but experimenting everyday.

Every week, since August 29, 2009, I get a sales report from my customer in raw form showing point of sales data for the previous 7 days. After manipulating the data I end up with 11 columns.

Now, if I am only concerned with that one week then I can drop the whole sheet into a Pivot Table and all is well.

However, I have 12 sheets now (with more to come), one for each week, and the row count ranges from 3,328 to 16,414.

I do not want to consolidate the data.

I can copy everything to one sheet, and hope not to max out at the million row mark, but would prefer to keep it tabbed by week.

View 2 Replies View Related

Combine Data From Multiple Worksheets - Pivot Tables

Sep 20, 2009

I am trying to combine sorted data from 2 worksheets to a new work sheet to process further. I have one worksheet with order number, part number, order qty but with different delivery dates. On another worksheet, I have the order number too, part number, the qty delivered. The qty delivered is not always the same each delivery.

My aim is to find out how many are already delivered under a certain purchase order and the balance of undelivered parts.

I used pivot tables to sort out the data but I am stuck here not knowing how to extra the sorted data from the pivot tables to the 3rd worksheet. I will need to match the order number and the part number.

View 9 Replies View Related

Find Minimum In Multiple Tables & Match To Heading

Jun 7, 2007

Referring to the post Find Min Value In Multiple Columns And Match The Header. I would like to go little bit further and see is there a way to find and match not just in multiple columns but in multiple tables (see the example)

View 4 Replies View Related

Search All Worksheets & Go To Found Values

Aug 30, 2007

I have a workbook with a sheet for every month. I have a searchbox searching for a client and jumping to that location. The problem I am having is that it takes me to the last occurence in the workbook if there is more that one client with the search criteria. Do you have any suggestions for a searchbox that whould take me to the first occurence and give me an option to go to the next occurence in the workbook. Please see the code below that I am using currently using.

Private Sub CommandButton1_Click()
Dim ws As Worksheet

Dim cl As Range, rng As Range
For Each ws In ActiveWorkbook.Worksheets

Set rng = ws.UsedRange
With rng
Set cl = .Find(Me.TextBox1.Value, LookIn:=xlValues)

If Not cl Is Nothing Then

Application.Goto cl

Else

View 7 Replies View Related

Search 1 Column All Worksheets & Go To Found Values

Aug 28, 2007

I need a VBA script that can display a search box on multiple sheets within the same workbook similar to using ctrl + F and search values in column B only. If there is text or the row happens to be empty then it should skip that and only search numbers. Also the numbers in column B range from 50000 to 89000 and if there is a wrong number entered then I want to have a pop up box saying Error: invalid value or something like that.

Sub search_box()
Range("B49000").Select
Cells.Find(What:="some#", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Cells.FindNext(After:=ActiveCell).Activate
End Sub

View 8 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 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.

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

Search A Workbook With Multiple Worksheets

Mar 28, 2008

Need MACRO to search a workbook with multiple worksheets?

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:
Row 2 - SONIC DRIVE IN#1531 AUSTIN TXDEBIT FOR CHECKCARD XXXXXX5623 DEBIT
Row 3 - JASON'S DELI # 125 Q64 AUSTIN TXDEBIT FOR CHECKCARD XXXXXX9718 DEBIT

View 4 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:

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

[Code]....

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

Excel 2010 :: Creating Multiple Tables In VBA And Referring To Newly Created Tables?

Jul 1, 2013

Using Excel 2010. I'm writing a macro that sets up a workbook to be used for estimating at the beginning of a project. In the code I need to create multiple tables (formerly known as "lists") in the workbook. Then later in the code I need to refer back to those newly created tables. Currently, the code that creates the table is part of a loop that creates the table on many different worksheets. The problem of course, is that I have to name the Table, and then it won't create a table of the same name on the next sheet. Then, later in the code, I need to make adjustments to the table that was just created before looping to the next sheet.

Is there a way to create a table without giving it a constant name? Or by giving it a name that builds off of other info in the sheet? For example, I would be good with the naming the table after the sheet name: "Sheet1_Table" or such.

Code:
Sub Auto_Open()
'
Dim sht As Worksheet
If Range("A1") = 1 Then

[Code].....

View 2 Replies View Related

Search Multiple Values In A Cell With Multiple Criteria

May 22, 2009

I am trying to search for multiple values in a cell with Multiple criteria.

E.g: Please find attached the sample excel data.

I tried using VLOOKUP which is not working as I am having multiple values in column A.

View 9 Replies View Related

Excel 2007 :: Copy Multiple Unique Values To Multiple Worksheets

Aug 26, 2013

I have an Excel Spreadsheet (2007) that contains over 500,000 records that shows Electric meter usage per month over a 24 month period per meter. What I want to be able to do is to select a meter row per number and copy is to it's own worksheet. At the end - I want to be able to have a seperate worksheet per electric meter number - that I can create a graph. If I go through all 9000 meters and copy and paste into a different worksheet - it will take me weeks to do manually. How can I do this automatically?

View 1 Replies View Related

Find Across Multiple Worksheets

Aug 9, 2006

I am trying to create a search/find form that will allow a user to find either a file number or client name across multiple sheets (from an array). However I can only get it to search the first sheet only....

View 4 Replies View Related

Find Non-Zero Value From Multiple Worksheets

Feb 8, 2007

Attached a Sheet. On the summary page, I need "C7" to look at C7 in all the other sheets, find the sheet that doesn't have a value of "0" in C7, and display that value.

View 2 Replies View Related

Find To Search And Return Multiple Dates

Jul 31, 2007

I am working with a macro into which you input a date, the macro searches for that date (in column D of the data sheet), then copies any row with that date onto a new sheet.

This works great, but now I need to be able to search for more than one date at a time and return any rows that cantain ANY of those dates. For example, I would want to search for any row containing 01/01/07 - 01/07/07 or any row containing 01/01/07, 01/02/07, 01/03/07...01/07/07.

View 9 Replies View Related

Find Across Multiple Sheets/Worksheets

Sep 3, 2006

Is there a way to use the Find tool (Ctrl + F) to search across multiple tabs (they may be called worksheets) in a single workbook?

View 2 Replies View Related

Search For Multiple Values?

Apr 6, 2013

Is it possible to search for multiple values (and present the result in a certain cell) within a range that spreads over two columns? For example: between A10 to A15 and B1 to B10.

View 6 Replies View Related

How To Find Last Populated Cell In A Row In Multiple Worksheets

Jan 12, 2013

I have a spreadsheet with many worksheets, everyone except the first representing a year. Every worksheet has twelve tables (the months), the first row of each table has the days of the month and the second row has the data that I put on a daily basis. The first worksheet calculates statistics using the data from the other worksheets.

I am looking for a formula that returns the date by finding the last populated cell and looking right above it, so I can use this info in the first worksheet to show when the spreadsheet was last updated.

View 2 Replies View Related

Search Multiple Sheets Within Report And Then Find Average Of Result?

Jun 6, 2014

Average.xlsxHi

I have changed the attachment to use CSV's

I am trying to create a report that I can enter the codes I am looking for and the formula will search the sheets within the report and once the results are found then find the average of those results.

What I am trying to do is search the codes from Sheet 1 under Outlet and ESA(in Blue) in sheet A,B and C. The problem is the ESA code seen in column I of A,B,C is only used when column L is empty in A,B,C.

Once the relevant codes are found I then need to look for the KPI's seen in sheet 1 B4, B6 and B8, they can be found in A,B,C in column R. then the result comes from column S in A,B or C.

Once the result is found for each code I want to find the average of them, with the answer to populate in yellow in sheet 1

I want the formula to be able to handle more or less codes as well as adjust the formula so I can add more sheets(possible D,E,F,G etc)

View 5 Replies View Related

Macro To Find And Fill Data With Multiple Search Option?

Jan 18, 2014

I am using the following code to find a NAME in my work sheet and to enter their status. But this macro is not compatible if a duplicate or Similar name exist.What I need is I need this macro to activate the search NAME Cell and then a VB box with OK & NEXT button has to appear. if the activated NAME is exactly the one I am searching, then I will click the OK button then it should display the "Enter Employee Status" box and as continue in my code, But If I press NEXT, the macro should search the next similar NAME and activate it, This process should continue until I click the OK button. (Same like CTRL+F function with an additional Status entry button)

Code:
Sub Button1848_Click()
Application.ScreenUpdating =False
Dim BeginCol AsLong

[Code]....

View 4 Replies View Related

Adjacent Cells - Search For Specific Values And Find The Total Number Of Instances

Jan 30, 2014

Any method to determine the number of instances a value ("4" for example) appears next to a specific value ("x" for example). I don't have a workbook; I'm just looking for any formula that can search for specific values and find the total number of instances that an adjoining cell contains data.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved