Lookup & return multiple rows based on cell value

[Stacie2410]

Good morning! I have a workbook that has 6 worksheets.

1.) Properties
2.) Owner Elections
3.) Agreement Tracking
4.) Routing Sheet
5.) Contract Info
6.) Owners

On the Routing Sheet worksheet, in B17, I need to place a formula that will
do the following:

Go to the Owner Elections worksheet, Look in Column A for the same value
that is entered in D10 of the Routing Sheet worksheet, once it finds that
value, then goes to column B and copies all rows (beginning with the next
row) under it, until it reaches the row that says "TOTAL", and pastes them in
the rows on the Routing Sheet, beginning with B17.

It's possible that it could be 2 rows, or it could be 10 rows, but I've
allowed enough space for the maximum number of rows that could be returned.

Is this possible? If I need to provide a sample of my workbook, please let
me know how to do so. I've tried a number of different things, but can't
seem to find something that works quite right. Your assistance is greatly
appreciated!!

 

[Otto Moehrbach]

Stacie
A formula is not going to do it. A formula can only return a value and
it can return that value to only the cell in which it resides. You will
need VBA programming to do what you want.. What do you want to use as the
trigger to make all this happen? Maybe when an entry is made in D10 of the
Routing sheet? If you wish, send me your file via email. Include a copy of
this post as well as your original post. Tell me the version of Excel you
are running. My email is (e-mail address removed). Remove the "extra"
from this address. HTH Otto

 

[Ashish Mathur]

Hi,

1. Will the entry in cell D10 of the Routing sheet sheet appear as many
times in column A of the Owner elections sheet as the values in column B;
2. Will Total appear in column A or column B of the Owner elections sheet

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Stacie2410]

No, the data from D10 will not appear as many times in Column A of the Owner
Elections sheet as the values in Column B because the Owner Elections sheet
isn't really set up as a spreadsheet as much as it holds blocks of data. For
each block, the top row holds a property name in column A (which is the data
that is entered into D10 of the Routing Sheet), the next several rows contact
an owner number in column A, an owner name in column B, owner percent in
column C, and an owner election in column D. Then the bottom row is blank in
Column A, says "Total" in column B, and has a sum of the owners percent in
column C, and is blank in column D. Then there is a blank row, then the next
block of data begins.