PLEASE HELP!!!! A-Level student and teachers brains have exploded over this problem

John,
Okay, you have a couple of issues here. The main one is this listing of all shoes in that size. Constructing the list is not that difficult, the problem is that without VBA code its going to be ugly. If you imagine that you will have to have some kind of lookup formula for every possible size of shoe then if they increase their range you would have to add more formulae.

However, saying that it is possible for demonstration purposes and no doubt you will be required to evaluate for practical purposes.

I assume where your snagging is in finding the next match after finding the first. Check out a function called INDIRECT this allows you to construct references in text form and use them in formulae. Thus greater flexibility. I suggest you use this in conjunction with MATCH and OFFSET. MATCH is used to locate the position of something within a range and return its list number. OFFSET is then used to display that item. Beware of the reference points or you can get out of sync. You can then create a cascade on the sheet that matches the first then using INDIRECT contributes the new range. And So On down.

EG.

Say you are matching a list A1:A10 the first match is in A2 using INDIRECT you can make your next formula MATCH in range A3:A10 and so on down the cascade. You can also add in logical statements or IF statements to handle when you run out of matchs or no matchs at all.

As I said earlier writing a MACRO to run the task would be cleaner. You could simply scan down your stock list and write all matches to the appropriate place. A couple of FOR...NEXT loops to track relative positions and Range("SheetName!A1").value to read/write to the approprite places.

I hope Ive given you some ideas to work with.

Rob

Microsoft Access or another database program would suit you better (nm)

Have you thought about using a Pivot table for this?

Try this code for starters

Build a workbook with two sheets, rename the sheets: Stock & Found.
On sheet "Stock" Lable A1 "Size"
Lable B1 "Name"
Lable C1 "Stock"
Add your inventory data below your labels, Size=the shoe sizes in stock; Like: 2,2.5,3,3.5...
Name=the style of shoe name; Like: Candie2, Patio-A1, etc.
Stock=the number of sets you have in stock; Like: 0, 1, 2, 3,...

Then build your search list, also on the sheet: "Stock"
F4 is your pulldown list of sizes, list all the sizes you will have. Format cell as text.
In F3 lable "Search"
In E4 lable "Size:.."
In G4 Lable "....<== Use pull down list!"

Centered on F6 build a form buttom and attach the code below. Lable the button "Display Inventory"
This should give you something to work with. JSW

Sub myFind()
'Find by size

With Worksheets("Stock")
.AutoFilterMode = False
.Range("A1").AutoFilter
.Range("A1").AutoFilter Field:=1, Criteria1:=Range("F4"), VisibleDropDown:=False
.UsedRange.SpecialCells(xlCellTypeVisible).Copy _
Destination:=Sheets("Found").Cells(1, 1)
Application.CutCopyMode = False
.AutoFilterMode = False
End With
Sheets("Found").Select
Range("A1").Select
End Sub

The above code is a little slow, and could be improved, but it works. JSW

Re: Microsoft Access or another database program would suit you better (nm)

Wouldn't a simple filter do the trick? If you select size 6 for eg. then only size 6 would display including all the different styles. I don't know maybe I misread your request...

Ziggy