Hello,

I have an existing spreadsheet that contains account information such as
account number, name, rating loan balance, net balance, etc... This
information is contained in a worksheet named Data. I need to create another
report from some of the info on the Data tab. I have created a new tab named
"Watch". I need to get all account numbers, name, rating, and balances where
the rating of the account = 5. I need help with a formula that will enable me
to get the information I need contained on the Data tab onto the Watch tab.
Also, when this report gets updated I would like the info on the Watch tab to
also get updated. Any help with this will be greatly appreciated. If you need
more details just reply to this post and I will be sure to provide what is
needed. Thank You.
Dave Y

One formulas play which gives you the flexibility to extract in your "Watch"
sheet based on the desired rating

Illustrated in this sample:
Extract lines with specified rating in new sht.xls
http://www.freefilehosting.net/download/3ack2

Source data assumed in cols A to E in sheet: x,
data from row2 down, with the key col = col C (Ratings)

In another sheet: Watch,
A data validation list to select ratings: 1-5
is created in A1

In B2: =IF(x!C2=$A$1,ROW(),"")
Leave B1 blank

In C2:
=IF(ROWS($1:1)COUNT($B:$B),"",INDEX(x!A:A,SMALL($ B:$B,ROWS($1:1))))
Copy C2 to G2. Select B2:G2, copy down to cover the max expected extent of
source data in x, say down to G200? Minimize/mask col B. Cols C to G will
return only the lines for the rating selected in A1 from x, with all lines
neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dave Y" wrote:
I have an existing spreadsheet that contains account information such as
account number, name, rating loan balance, net balance, etc... This
information is contained in a worksheet named Data. I need to create another
report from some of the info on the Data tab. I have created a new tab named
"Watch". I need to get all account numbers, name, rating, and balances where
the rating of the account = 5. I need help with a formula that will enable me
to get the information I need contained on the Data tab onto the Watch tab.
Also, when this report gets updated I would like the info on the Watch tab to
also get updated. Any help with this will be greatly appreciated. If you need
more details just reply to this post and I will be sure to provide what is
needed. Thank You.
Dave Y

Hi Max,

Thank you forthe reply. I will play with that formula as soon as I get the
chance. I greatly appreciate your assistance.

"Max" wrote:

One formulas play which gives you the flexibility to extract in your "Watch"
sheet based on the desired rating

Illustrated in this sample:
Extract lines with specified rating in new sht.xls
http://www.freefilehosting.net/download/3ack2

Source data assumed in cols A to E in sheet: x,
data from row2 down, with the key col = col C (Ratings)

In another sheet: Watch,
A data validation list to select ratings: 1-5
is created in A1

In B2: =IF(x!C2=$A$1,ROW(),"")
Leave B1 blank

In C2:
=IF(ROWS($1:1)COUNT($B:$B),"",INDEX(x!A:A,SMALL($ B:$B,ROWS($1:1))))
Copy C2 to G2. Select B2:G2, copy down to cover the max expected extent of
source data in x, say down to G200? Minimize/mask col B. Cols C to G will
return only the lines for the rating selected in A1 from x, with all lines
neatly bunched at the top.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dave Y" wrote:
I have an existing spreadsheet that contains account information such as
account number, name, rating loan balance, net balance, etc... This
information is contained in a worksheet named Data. I need to create another
report from some of the info on the Data tab. I have created a new tab named
"Watch". I need to get all account numbers, name, rating, and balances where
the rating of the account = 5. I need help with a formula that will enable me
to get the information I need contained on the Data tab onto the Watch tab.
Also, when this report gets updated I would like the info on the Watch tab to
also get updated. Any help with this will be greatly appreciated. If you need
more details just reply to this post and I will be sure to provide what is
needed. Thank You.
Dave Y

Welcome, Dave Y. Thanks for feeding back.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dave Y" wrote in message
...
Hi Max,

Thank you for the reply. I will play with that formula as soon as I get
the
chance. I greatly appreciate your assistance.