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 a "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 a "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.

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.

"ladygr" wrote:

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 a "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 a "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.

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

On Nov 20, 10:44 pm, ladygr wrote:
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.



"ladygr" wrote:
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 a "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 a "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.- Hide quoted text -

- Show quoted text -

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" wrote:

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

On Nov 20, 10:44 pm, ladygr wrote:
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.



"ladygr" wrote:
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 a "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 a "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.- Hide quoted text -

- Show quoted text -

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

Pete

On Nov 21, 9:50 am, ladygr wrote:
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" wrote:
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

On Nov 20, 10:44 pm, ladygr wrote:
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.

"ladygr" wrote:
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 a "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 a "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.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

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

"Pete_UK" wrote:

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

Pete

On Nov 21, 9:50 am, ladygr wrote:
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" wrote:
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

On Nov 20, 10:44 pm, ladygr wrote:
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.

"ladygr" wrote:
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 a "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 a "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.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -