L
Luis Verdejo
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,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.
Thanks in Advance.
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,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.
Thanks in Advance.