On one tab of my spreadsheet, I have the following data in Columns
Purch Date / Maturity Date/ Par Val/ Interest Rate/ Interest

On a seperate tab of the spreadsheet, I'd like to have my data automatically
populated from the first tab based on the Purch Date.

For example, if I have 5 lines of data on tab one...and three of the lines
have the same purch date, I'd like excel to automatically list this data on
tab two once I change a date on tab two.

I've tried using Index and Lookup formulas, but they will only list the
first line item that matches this Purch Date...not the subsequent data.

How can I have these other lines of data extracted to the 2nd tab without
duplicating lines...and without omitting lines?

One way using non-array formulas to achieve it dynamically ..

Source data is assumed in sheet: X,
from row2 down in cols A to E, with col A = key col (Purchase Date)

In another sheet: Y (say),

Paste the same col headers into A1:E1
Let's say G1 will house the input of the purchase date (of interest)

Put in A2:
=IF(ROW(A1)COUNT($F:$F),"",INDEX(X!A:A,MATCH(SMAL L($F:$F,ROW(A1)),$F:$F,0)))
Copy A2 to E2

Put in F2:
=IF(X!A2="","",IF(X!A2=$G$1,ROW(),""))
(Leave F1 empty)

Then just select A2:F2 and copy down to cover the max expected extent of
data in X. Format cols A & B as dates. Hide away the criteria col F if
desired. Cols A to F will return the required results dynamically from X, ie
only the lines with purchase dates equal to that input in G1. All results
will be neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Brian" wrote:
On one tab of my spreadsheet, I have the following data in Columns
Purch Date / Maturity Date/ Par Val/ Interest Rate/ Interest

On a seperate tab of the spreadsheet, I'd like to have my data automatically
populated from the first tab based on the Purch Date.

For example, if I have 5 lines of data on tab one...and three of the lines
have the same purch date, I'd like excel to automatically list this data on
tab two once I change a date on tab two.

I've tried using Index and Lookup formulas, but they will only list the
first line item that matches this Purch Date...not the subsequent data.

How can I have these other lines of data extracted to the 2nd tab without
duplicating lines...and without omitting lines?

Max,
Thanks very much for your assistance....I think this is exactly what I
need...but for some reason I still can't get it to work correctly.
I did a test using the example you listed below....but when I try the same
thing with the actual spreadsheet, my data field come up blank.
The only revisions I made to your example below is the following:
My Column Headers run from column A7 to Column J7.
I then have column K represent column F in your example
And instead of putting my date in G1...I have it in C3 instead.

My formulas in column a thru J a
=IF(ROW(A7)COUNT($K:$K),"",INDEX(Investments!A:A, MATCH(SMALL($K:$K,ROW(A7)),$K:$K,0))) ....The A's change based on the column this formula is in.

My formula in column K is
=IF(Investments!A7="","",IF(Investments!A7=$C$3,RO W(),""))

Any idea why this still isn't working?

Thanks again...I really appreciate it!!!

"Max" wrote:

One way using non-array formulas to achieve it dynamically ..

Source data is assumed in sheet: X,
from row2 down in cols A to E, with col A = key col (Purchase Date)

In another sheet: Y (say),

Paste the same col headers into A1:E1
Let's say G1 will house the input of the purchase date (of interest)

Put in A2:
=IF(ROW(A1)COUNT($F:$F),"",INDEX(X!A:A,MATCH(SMAL L($F:$F,ROW(A1)),$F:$F,0)))
Copy A2 to E2

Put in F2:
=IF(X!A2="","",IF(X!A2=$G$1,ROW(),""))
(Leave F1 empty)

Then just select A2:F2 and copy down to cover the max expected extent of
data in X. Format cols A & B as dates. Hide away the criteria col F if
desired. Cols A to F will return the required results dynamically from X, ie
only the lines with purchase dates equal to that input in G1. All results
will be neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Brian" wrote:
On one tab of my spreadsheet, I have the following data in Columns
Purch Date / Maturity Date/ Par Val/ Interest Rate/ Interest

On a seperate tab of the spreadsheet, I'd like to have my data automatically
populated from the first tab based on the Purch Date.

For example, if I have 5 lines of data on tab one...and three of the lines
have the same purch date, I'd like excel to automatically list this data on
tab two once I change a date on tab two.

I've tried using Index and Lookup formulas, but they will only list the
first line item that matches this Purch Date...not the subsequent data.

How can I have these other lines of data extracted to the 2nd tab without
duplicating lines...and without omitting lines?