Recalling more than one cell by inputting a single command

  • Thread starter Thread starter Luis Verdejo
  • Start date Start date
L

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.
 
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
 
Back
Top