to pull from multiple sheets-index,match,vlookup,if,and,or???

[ladygr]

I'm soooo overwhelmed. I have some experience with functions, but I
appreciate the responses I have received from this forum in the past that got
me out of a confusing dilema. I have another situation I would like help
with. It is basically a library scenerio. I have 5 worksheets in one
book-"Overview, Status, Fines, Patron, and Overdue".
"Overview" is where I would like ALL information pulled TO. It has column 1
as labels-A1 "paton ID", A2 "patron name", A3 "address", A4 "city/state/zip",
A5 "outstanding fines", (sub labels-B6 "fine", C6 "reason", D6 "copyID", E6
"Title") (I've left row 7 blank for formulas and 8 blank for spacing
purposes.), A9 "books on loan" (sub labels-B10 "status", C10 "DueDate", D10
"copyID", E10 "Title", F10 "cost") (I've left row 11 for formulas and 12
blank , again just for spacing). A closing remark is on row 13. Columns B:F
is the range I would like automatically populated from the other sheets, all
based on the patronID # MANUALLY input into B1. Areas of "outstanding fines"
and "books on loan" will hopefully populate under the sub-label column
headings and will need to automatically add rows for additional entries
because each patron will definitely have between 4 and 13 (maybe more) books
on-loan and may have multiple fines (maybe for the same book or single fines
for multiple books or may have no fines at all, which I would like it to then
state "no ourstanding fines due".
The other sheets have the labels across row 1 and data beginning in row 2
extending down the sheets. The "status" sheet has labels: "copyID", "title",
"status", "patronID" and "dueDate". There are almost 55,000 rows of data on
this one sheet, sorted by copyID. The "Fines" sheet labels are: "patronID",
"patronName", "copyID", "fine", and "reason". The number of rows vary
month-to-month so I would need that to remain adjustable, sorted by patronID.
"Patron" sheet labels are: "patronName", "name", "address", "city", "zip",
and "phone" (state of CA is assumed) with approx 3,000 rows of data which may
also change as patrons leave/arrive, also sorted by name. The last sheet
"Overdue" has same column labels as "Status", but has an additional "cost"
label, sorted by name.
I have been looking at IF, AND, OR, VLOOKUP. But, have now been seeing alot
of INDEX and MATCH. I think that will work better for me, but am unfamiliar
with their inputs. I have been looking at the forums and following links and
using HELP. But have gotten myself totally confused and overwhelmed. I want
to stay with functions-no VBA or programming please. Could you help me
untangle my mind??? Ant assistance is GREATLY appreciated.

 

[ladygr]

After re-reading my post I realized that is sounds like alot to ask, but I
don't expect a complete workbook. What I am looking for is the syntax of
INDEXing data on other worksheets, MATCHing the information to the input data
(patronID) and fill in missing data. I would also like an explanation of the
why's and wherefore's of the syntax so I can reason them out for other areas.
Again, thank you.

 

[Pete_UK]

Your first post was a lot to take in. If you would like to send me a
slimmed-down and sanitised version of your workbook I'll take a look
at it. Please include notes in your worksheets as to what you want to
achieve and the typical volumes of data that you expect on that sheet
(so I don't have to keep looking elsewhere). I don't have Excel 2007,
so please send an .xls file to:

pashurst <at> auditel.net

Change the obvious.

Pete

 

[ladygr]

Pete,

Thank you for responsing. I've sent a sample portion .xls file from
Excel2002 for your review. The notes are on the active sheet for what I am
hoping to accomplish. the other sheets are passive and will not available to
others, but I will be maintaining current information in them, so some
information may change, but not the column labels or type of information in
each column.

The red ID# is what is input (and matched on the other sheets), the green is
what I would like automatically populated, and the blue are the notes (blue
notes will not be in the final sheet). As far as the volume, the "copies"
sheet has the largest volume with almost 55,000 rows of data. It is fairly
stable as far as the count, for now. That could change though. The number of
"fines" will vary, so I hope the formulas can be variable in the # of rows to
allow for updates of those sheets without having to change the "overview"'s
formulas. The "patrons" will be about 4,000 and, again, will vary slightly as
well (+/- a few hundred).

As I stated, this is probably a straight-forward process, I've just confused
myself too much. But I would be even worse off if you guys were'nt here to
bail people like me out. Thank you.

 

[Pete_UK]

Have received the file and had a quick look through - will get
something back to you later on.

Pete

 

[ladygr]

Pete,
Thank you-am looking forward to your assistance. Happy Thanksgiving.

 

[Pete_UK]

I've just sent the file back to you with all the features you
requested.

Pete

 

[ladygr]

Pete,
Everything works GREAT! You are truly a pro and make it look so easy. I am
also thankful for your explanations to help me better understand the syntax.
I was haveing trouble with the multiple patron entries. The sequential
formula column is just what was needed to make it all work together. Simple
but effective. I only wish your solutions could be posted here to help
others. Thank you. I very much appreciate your time and expertise.

 

[ladygr]

is there any way to modify the formulae so that if a copy is in the Fines
section with a fee charged AND the reason is LOST, it will not be listed in
the On Loan section too? I do want it in the On Loan section if status is
lost, but no fine has been charged yet, though. I just don't want it there if
the fee has already been charged.

 

[Pete_UK]

Well. thanks for the feedback - I'm glad it works for you.

If I get chance later on I'll summarise the pertinent features.

Pete

 

[Pete_UK]

This would be difficult to accomplish as the loans sheet and the fines
sheet are not linked in any way - the linking is all back to the
borrower and could be in any sequence. So, it could be loan number 4
which is lost, but this could be any number in the fines section, and
you could have several fines relating to that particular loan, which
again could be in any order.

I suppose the sequential formula might be amended to return a blank if
the status is lost AND fine has been charged, but that would then mess
up the formula for later fines for that patron.

Pete