Referencing cells and copy rows of data back to summary sheet
 

I need to know how to write a formula were it searches the numerous sheets
for a specific cell reference and is able to copy all the information in the
ROW matching the specific cell requirements on the one sheet. For instance
I am searching for "DM" on all the sheets and if it has a cell that has "DM"
in it I want to return all the information in that ROW back to the summary
sheet. Thank you for your time and assistance. Hope this question makes
sence.

Referencing cells and copy rows of data back to summary sheet
 

Will "DM" only occur once in all the sheets, or might there be other
instances of "DM" on other sheets (or even multiple occurrences in the
same sheet)? How many sheets do you have, and what naming convention
do you use? How many rows of data do you have on each sheet?

Your question makes more sense now that you have asked for data from
the same ROW that "DM" occurs on, rather than COLUMN in your earlier
posting.

Pete

On Oct 12, 8:56 pm, MaggieR wrote:
I need to know how to write a formula were it searches the numerous sheets
for a specific cell reference and is able to copy all the information in the
ROW matching the specific cell requirements on the one sheet. For instance
I am searching for "DM" on all the sheets and if it has a cell that has "DM"
in it I want to return all the information in that ROW back to the summary
sheet. Thank you for your time and assistance. Hope this question makes
sence.

Referencing cells and copy rows of data back to summary sheet
 

Pete_UK

"DM" will show on multiple sheets approx 20-25 sheets and multiple times on
each sheet "DM" is the sales representative and the sheets are the different
vendors ex.

"DM" summary sheet has 15 different entries on one or more of the 20-25
sheets i.e Century vendor name (individual Sheet for the vendor).

So I want it to search all 20-25 sheets and anywhere the initials "DM"
appear I want it to copy all the information in the row to reflect back to
his summary sheet. I bet the ROW did clear up my question from earlier and
thank you again for your assistance. Please let me know if you have any other
questions!

"Pete_UK" wrote:

Will "DM" only occur once in all the sheets, or might there be other
instances of "DM" on other sheets (or even multiple occurrences in the
same sheet)? How many sheets do you have, and what naming convention
do you use? How many rows of data do you have on each sheet?

Your question makes more sense now that you have asked for data from
the same ROW that "DM" occurs on, rather than COLUMN in your earlier
posting.

Pete

On Oct 12, 8:56 pm, MaggieR wrote:
I need to know how to write a formula were it searches the numerous sheets
for a specific cell reference and is able to copy all the information in the
ROW matching the specific cell requirements on the one sheet. For instance
I am searching for "DM" on all the sheets and if it has a cell that has "DM"
in it I want to return all the information in that ROW back to the summary
sheet. Thank you for your time and assistance. Hope this question makes
sence.

Referencing cells and copy rows of data back to summary sheet
 

Excel is not very good at this kind of thing - Access would be better.

If you were to use MATCH to try to find "DM" on one sheet then it will
return #N/A if it is not present, or it will return the (relative) row
of the first occurence of "DM" if it is present. This can be used in
conjunction with INDEX to return other data from that same row. The
problem comes with the second (and subsequent) occurence of "DM", as
the MATCH function will still return the row for the first occurence.

One way I have overcome this in the past is, for the second (and
other) occurences, to adjust the range that is being searched so that
it starts with the row immediately after where the previous occurence
has been found - you can use INDIRECT to do this. However, the
formulae required for this are quite involved and are fairly bespoke
for each particular use, rather than generic.

Obviously, your situation is even more complex because you want to
look into 20-25 sheets. Presumably you also want to bunch all the data
into contiguous rows, rather than, say, allocate 20 rows for each
potential sheet where "DM" might appear (although you could take this
approach and use a custom filter to only display rows with data in
them).

Quite a big project - I hope my comments have been of some use to you.

Pete

On Oct 15, 7:40 pm, MaggieR wrote:
Pete_UK

"DM" will show on multiple sheets approx 20-25 sheets and multiple times on
each sheet "DM" is the sales representative and the sheets are the different
vendors ex.

"DM" summary sheet has 15 different entries on one or more of the 20-25
sheets i.e Century vendor name (individual Sheet for the vendor).

So I want it to search all 20-25 sheets and anywhere the initials "DM"
appear I want it to copy all the information in the row to reflect back to
his summary sheet. I bet the ROW did clear up my question from earlier and
thank you again for your assistance. Please let me know if you have any other
questions!



"Pete_UK" wrote:
Will "DM" only occur once in all the sheets, or might there be other
instances of "DM" on other sheets (or even multiple occurrences in the
same sheet)? How many sheets do you have, and what naming convention
do you use? How many rows of data do you have on each sheet?

Your question makes more sense now that you have asked for data from
the same ROW that "DM" occurs on, rather than COLUMN in your earlier
posting.

Pete

On Oct 12, 8:56 pm, MaggieR wrote:
I need to know how to write a formula were it searches the numerous sheets
for a specific cell reference and is able to copy all the information in the
ROW matching the specific cell requirements on the one sheet. For instance
I am searching for "DM" on all the sheets and if it has a cell that has "DM"
in it I want to return all the information in that ROW back to the summary
sheet. Thank you for your time and assistance. Hope this question makes
sence.- Hide quoted text -

- Show quoted text -

Referencing cells and copy rows of data back to summary sheet
 

Pete can you give me a formula using the INDIRECT operation using the
parameters previously mentioned; would this also be a INDIRECT MATCH
sequence?

"Pete_UK" wrote:

Excel is not very good at this kind of thing - Access would be better.

If you were to use MATCH to try to find "DM" on one sheet then it will
return #N/A if it is not present, or it will return the (relative) row
of the first occurence of "DM" if it is present. This can be used in
conjunction with INDEX to return other data from that same row. The
problem comes with the second (and subsequent) occurence of "DM", as
the MATCH function will still return the row for the first occurence.

One way I have overcome this in the past is, for the second (and
other) occurences, to adjust the range that is being searched so that
it starts with the row immediately after where the previous occurence
has been found - you can use INDIRECT to do this. However, the
formulae required for this are quite involved and are fairly bespoke
for each particular use, rather than generic.

Obviously, your situation is even more complex because you want to
look into 20-25 sheets. Presumably you also want to bunch all the data
into contiguous rows, rather than, say, allocate 20 rows for each
potential sheet where "DM" might appear (although you could take this
approach and use a custom filter to only display rows with data in
them).

Quite a big project - I hope my comments have been of some use to you.

Pete

On Oct 15, 7:40 pm, MaggieR wrote:
Pete_UK

"DM" will show on multiple sheets approx 20-25 sheets and multiple times on
each sheet "DM" is the sales representative and the sheets are the different
vendors ex.

"DM" summary sheet has 15 different entries on one or more of the 20-25
sheets i.e Century vendor name (individual Sheet for the vendor).

So I want it to search all 20-25 sheets and anywhere the initials "DM"
appear I want it to copy all the information in the row to reflect back to
his summary sheet. I bet the ROW did clear up my question from earlier and
thank you again for your assistance. Please let me know if you have any other
questions!



"Pete_UK" wrote:
Will "DM" only occur once in all the sheets, or might there be other
instances of "DM" on other sheets (or even multiple occurrences in the
same sheet)? How many sheets do you have, and what naming convention
do you use? How many rows of data do you have on each sheet?

Your question makes more sense now that you have asked for data from
the same ROW that "DM" occurs on, rather than COLUMN in your earlier
posting.

Pete

On Oct 12, 8:56 pm, MaggieR wrote:
I need to know how to write a formula were it searches the numerous sheets
for a specific cell reference and is able to copy all the information in the
ROW matching the specific cell requirements on the one sheet. For instance
I am searching for "DM" on all the sheets and if it has a cell that has "DM"
in it I want to return all the information in that ROW back to the summary
sheet. Thank you for your time and assistance. Hope this question makes
sence.- Hide quoted text -

- Show quoted text -

Referencing cells and copy rows of data back to summary sheet
 

I did say that the formulae are quite specific to the particular setup
you have, so if you could describe things in a bit more detail then I
can mock something up for you.

For example,are your vendor sheets identical in layout, and what is
that layout (number of header rows, columns used, which column to
search for "DM" in, typical number of rows used on vendor sheets, what
columns do you want returned to your salesman sheet, number of
salesman sheets, etc.)

If it is easier, you could send me a sample of your file (desensitize
it first), to:

pashurst <at auditel.net

Change the obvious.

Pete

On Oct 16, 5:50 pm, MaggieR wrote:
Pete can you give me a formula using the INDIRECT operation using the
parameters previously mentioned; would this also be a INDIRECT MATCH
sequence?



"Pete_UK" wrote:
Excel is not very good at this kind of thing - Access would be better.

If you were to use MATCH to try to find "DM" on one sheet then it will
return #N/A if it is not present, or it will return the (relative) row
of the first occurence of "DM" if it is present. This can be used in
conjunction with INDEX to return other data from that same row. The
problem comes with the second (and subsequent) occurence of "DM", as
the MATCH function will still return the row for the first occurence.

One way I have overcome this in the past is, for the second (and
other) occurences, to adjust the range that is being searched so that
it starts with the row immediately after where the previous occurence
has been found - you can use INDIRECT to do this. However, the
formulae required for this are quite involved and are fairly bespoke
for each particular use, rather than generic.

Obviously, your situation is even more complex because you want to
look into 20-25 sheets. Presumably you also want to bunch all the data
into contiguous rows, rather than, say, allocate 20 rows for each
potential sheet where "DM" might appear (although you could take this
approach and use a custom filter to only display rows with data in
them).

Quite a big project - I hope my comments have been of some use to you.

Pete

On Oct 15, 7:40 pm, MaggieR wrote:
Pete_UK

"DM" will show on multiple sheets approx 20-25 sheets and multiple times on
each sheet "DM" is the sales representative and the sheets are the different
vendors ex.

"DM" summary sheet has 15 different entries on one or more of the 20-25
sheets i.e Century vendor name (individual Sheet for the vendor).

So I want it to search all 20-25 sheets and anywhere the initials "DM"
appear I want it to copy all the information in the row to reflect back to
his summary sheet. I bet the ROW did clear up my question from earlier and
thank you again for your assistance. Please let me know if you have any other
questions!

"Pete_UK" wrote:
Will "DM" only occur once in all the sheets, or might there be other
instances of "DM" on other sheets (or even multiple occurrences in the
same sheet)? How many sheets do you have, and what naming convention
do you use? How many rows of data do you have on each sheet?

Your question makes more sense now that you have asked for data from
the same ROW that "DM" occurs on, rather than COLUMN in your earlier
posting.

Pete

On Oct 12, 8:56 pm, MaggieR wrote:
I need to know how to write a formula were it searches the numerous sheets
for a specific cell reference and is able to copy all the information in the
ROW matching the specific cell requirements on the one sheet. For instance
I am searching for "DM" on all the sheets and if it has a cell that has "DM"
in it I want to return all the information in that ROW back to the summary
sheet. Thank you for your time and assistance. Hope this question makes
sence.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -