Hello again, I got great help from another user Max, but can't get the
formula to work with my sheets. I get the overall jist of the formulas but
can't translate them to work with my sheets. Can someone help.

I have 2 sheets: 1st called Master and the 2nd called Enrolement.
the master has everything and i'm writing formulas in the second sheet to
extract specific information. in columnA is the course code arranged in
order columnB is the course name, in columnF are the book titles.

What I've done to date is write basic formulas to print out the course name
from a course number entered, how many books are needed for that course and
the total cost of books for a specific course, but cannot get a book list to
print out. As i've said the course number is referrenced for the course and
the books so when i run a search all that prints is the last book title
instead of say 10 books used for that course.

I was lucky enough to be helped but cannot change this formula which Max
gave me for my sheets! Below is the general formulas he gave, my problem is
translating these for my sheets, i.e. Master and Enrolement. can someone help?

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,SM ALL($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.

Thanks in Advance.

Simply change this Sheet1! for your sheet like this:
=IF(Master!B2="","",IF(Master!B2=$A$2,ROW(),""))

Is that what you meant?

Luis Verdejo;573964 Wrote:
Hello again, I got great help from another user Max, but can't get the
formula to work with my sheets. I get the overall jist of the formulas
but
can't translate them to work with my sheets. Can someone help.

I have 2 sheets: 1st called Master and the 2nd called Enrolement.
the master has everything and i'm writing formulas in the second sheet
to
extract specific information. in columnA is the course code arranged in
order columnB is the course name, in columnF are the book titles.

What I've done to date is write basic formulas to print out the course
name
from a course number entered, how many books are needed for that course
and
the total cost of books for a specific course, but cannot get a book
list to
print out. As i've said the course number is referrenced for the course
and
the books so when i run a search all that prints is the last book title
instead of say 10 books used for that course.

I was lucky enough to be helped but cannot change this formula which
Max
gave me for my sheets! Below is the general formulas he gave, my
problem is
translating these for my sheets, i.e. Master and Enrolement. can
someone help?

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,SM ALL($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.

Thanks in Advance.


--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=158596

Microsoft Office Help