Recalling more than one cell by inputting a single command

[Luis Verdejo]

Hello everybody,
I have a master list of books and course. each course has a course
number.for each course there are different number of books.the books are
linked to the course by the course number.
So in a new sheet when i enter the course number, for example 09001:
English, 10 books required,price all comes up without a problem. the problem
comes when i try to recall the book list. it cycles through and only prints
the last book in that section. i've been using the vlookup function.

Is there a way i can get excel to print out all the books by inputting the
course number. Do i group them together and recall the group? or do i have
to create many if/else like statements to start cycling through the list.

my brain is melting trying different formulas
thanks in advance.

 

[Max]

One simple way to extract it in another sheet

Assume the source table is in Sheet1, cols A to C, data from row2 down
where the key col = col B (course numbers)

In another sheet,
In A2 will be the input for the desired course number, eg: 1111
In B2: =IF(Sheet1!B2="","",IF(Sheet1!B2=$A$2,ROW(),""))
Copy B2 down to cover the max expected extent of data in Sheet1's col B, say
down to B500? Hide away/minimize this criteria col B.

Put in C2:
=IF(ROWS($1:1)>COUNT($B:$B),"",INDEX(Sheet1!A:A,SMALL($B:$B,ROWS($1:1))))
Copy C2 to E2, fill down just enough to cover the max expected number of
repeats for any course number, say down to E10. Cols C to E will return the
required results from Sheet1's cols A to C for the course number specified in
A2, with all lines neatly packed at the top. Paste over the col headers from
Sheet1 into C1:E1 to complete the extracted table. Success? hit the YES below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik