Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Index & Matching Functions
I have created a workbook with three sheets. The first contains data
pertaining to a project type and a specific ID number that I have assigned to it. For Example: 1 Areospace Ground Equipment Complexes 2 Hangers 3 Maintenance Facilities 4 Warehouse Etc..... I have another sheet that contains a database of all my firm's completed projects in which I detail several fields (i.e. project number, description, location, cost, client, and its Project Type ID#-taken from the list aforementioned list). My third sheet is the one that I have the problem with. I am wanting to type in the Project Type ID# and have Excel sort through the database on the second sheet and provide me with a list of entries on this sheet that match that Project Type ID# on the second sheet. I get it to where I use the Index fuction and Match function nested within (putting it where the row entry would go) but I only get the first entry that matches the Type ID not the rest. (In reallity there are serveral projects that have pertained to the same Type ID but different clients and different locations) Question: How can I get it to give me all the entries that match that specific Type ID that I have entered? Can this even be done in Excel? Any help? Thanks! E-mail: |
#2
|
|||
|
|||
"Erik Sauceda via OfficeKB.com" wrote: I have created a workbook with three sheets. The first contains data pertaining to a project type and a specific ID number that I have assigned to it. For Example: 1 Areospace Ground Equipment Complexes 2 Hangers 3 Maintenance Facilities 4 Warehouse Etc..... I have another sheet that contains a database of all my firm's completed projects in which I detail several fields (i.e. project number, description, location, cost, client, and its Project Type ID#-taken from the list aforementioned list). My third sheet is the one that I have the problem with. I am wanting to type in the Project Type ID# and have Excel sort through the database on the second sheet and provide me with a list of entries on this sheet that match that Project Type ID# on the second sheet. I get it to where I use the Index fuction and Match function nested within (putting it where the row entry would go) but I only get the first entry that matches the Type ID not the rest. (In reallity there are serveral projects that have pertained to the same Type ID but different clients and different locations) Question: How can I get it to give me all the entries that match that specific Type ID that I have entered? Can this even be done in Excel? Any help? Thanks! E-mail: Erik A database is better, but I played around with this in Excel a while ago. The solution seems to work but does not recognize every (invoice number). It Uses INDEX, MATCH LOOKUP and Address functions. If your are interested I'll send you a copy of the file; the formulas are hard to explain and you'll find it easier to examine the file Peter remove the NOSPAM |
#3
|
|||
|
|||
See if you can work off of something like this:
Sheet2, A1 = label "Proj I.D.Num." B1 = label "All Proj Names" A2:B100 = datalist Sheet3, A1 = cell to enter Proj. I.D.Num to search for. Enter this *array* formula in B1: =INDEX(Sheet2!$B$2:$B$100,SMALL(IF(Sheet2!$A$2:$A$ 100=$A$1,ROW($A$1:$A$99)), ROW(A1))) -- Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead oft the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. NOW, you copy the formula down Column B as *needed*. *NEEDED* means, just how many returns do you expect ... how many times is a Project ID number repeated in your datalist? You probably don't know exactly, so ... you should copy down more cells then you think you need. When this formula runs out of data to match, it returns a #NUM! error. Therefore you will *always* want to see at least one #NUM! error message, to insure that you have returned all possible matches. If no error is returned, drag down the formula until you *do* see one. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Erik Sauceda via OfficeKB.com" wrote in message ... I have created a workbook with three sheets. The first contains data pertaining to a project type and a specific ID number that I have assigned to it. For Example: 1 Areospace Ground Equipment Complexes 2 Hangers 3 Maintenance Facilities 4 Warehouse Etc..... I have another sheet that contains a database of all my firm's completed projects in which I detail several fields (i.e. project number, description, location, cost, client, and its Project Type ID#-taken from the list aforementioned list). My third sheet is the one that I have the problem with. I am wanting to type in the Project Type ID# and have Excel sort through the database on the second sheet and provide me with a list of entries on this sheet that match that Project Type ID# on the second sheet. I get it to where I use the Index fuction and Match function nested within (putting it where the row entry would go) but I only get the first entry that matches the Type ID not the rest. (In reallity there are serveral projects that have pertained to the same Type ID but different clients and different locations) Question: How can I get it to give me all the entries that match that specific Type ID that I have entered? Can this even be done in Excel? Any help? Thanks! E-mail: |
#4
|
|||
|
|||
Thanks....let me play around with these and I will let you know how things
work out.... Erik -- Message posted via http://www.officekb.com |
#5
|
|||
|
|||
RadDyer,
Can you explain a bit more on your suggesstion? I am trying it out but I don't understand the process. Thanks -- Message posted via http://www.officekb.com |
#6
|
|||
|
|||
Did you create a test data list on Sheet2, and copy the formula I posted to
B1 of Sheet3, and enter a Proj. ID number in A1 of Sheet3, that you *know* exists *more then once* in your test data list? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Erik Sauceda via OfficeKB.com" wrote in message ... RadDyer, Can you explain a bit more on your suggesstion? I am trying it out but I don't understand the process. Thanks -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match and index functions: corrlating data from 2 worksheets | Excel Worksheet Functions | |||
3 questions about automated c++ com add-in worksheet functions | Excel Worksheet Functions | |||
How do I use the Match and Index functions to look up a value tha. | Excel Worksheet Functions | |||
# of Functions per cell | Excel Worksheet Functions | |||
index to a range of cells | Excel Worksheet Functions |