Hello

I have list that i need to sort and a I can't use the filter to do it, I
want to use formulas to do it. What i want to do is use a formula to take all
the rows that have Table No "1" and display them starting in say column G. I
have been trying offsets, and lookups but I struggling with this. I can't get
the filter to work the way I want either. Any help would be appreciated

A B C D E

1 Table No. H Q Start Date End Date
2 1 2.5 25 2006-05-14 2005-06-14
3 1 3.5 35 2006-05-14 2005-06-14
4 2 3.0 30 2006-10-14 2005-12-14
4 2 4.0 40 2006-10-14 2005-12-14

When you said "I can't use the filter to do it". Do you mean, you are not
allowed to use it or you don't want to use it or it doesn't works when you
use it

To me, the easiest way is to use AutoFilter.

"Jamie" wrote:

Hello

I have list that i need to sort and a I can't use the filter to do it, I
want to use formulas to do it. What i want to do is use a formula to take all
the rows that have Table No "1" and display them starting in say column G. I
have been trying offsets, and lookups but I struggling with this. I can't get
the filter to work the way I want either. Any help would be appreciated

A B C D E

1 Table No. H Q Start Date End Date
2 1 2.5 25 2006-05-14 2005-06-14
3 1 3.5 35 2006-05-14 2005-06-14
4 2 3.0 30 2006-10-14 2005-12-14
4 2 4.0 40 2006-10-14 2005-12-14

The sheet is part of an automated workbook in which queries bring in the data
and then it is automatically sorted and run through various calculations, the
list size will change as changes are made in the database, for the most part
this will be a hands off spreadsheet controlled through a scheduler, the only
thing the person using the spreadsheet would change is on the first page I
have set up cells that are tied into lookups so they can change how lists are
sorted or searched. I hope this helps

"Teethless mama" wrote:

When you said "I can't use the filter to do it". Do you mean, you are not
allowed to use it or you don't want to use it or it doesn't works when you
use it

To me, the easiest way is to use AutoFilter.

"Jamie" wrote:

Hello

I have list that i need to sort and a I can't use the filter to do it, I
want to use formulas to do it. What i want to do is use a formula to take all
the rows that have Table No "1" and display them starting in say column G. I
have been trying offsets, and lookups but I struggling with this. I can't get
the filter to work the way I want either. Any help would be appreciated

A B C D E

1 Table No. H Q Start Date End Date
2 1 2.5 25 2006-05-14 2005-06-14
3 1 3.5 35 2006-05-14 2005-06-14
4 2 3.0 30 2006-10-14 2005-12-14
4 2 4.0 40 2006-10-14 2005-12-14

Hi Jamie

I would used Advanced Filter on your "first page" to pull across data
from the Main sheet.
Take a look at Debra Dalgleish's site for more help on this
http://www.contextures.com/xladvfilter01.html#ExtractWs

and there are several downloadable examples to help you at
http://www.contextures.com/excelfiles.html#Filter

--
Regards

Roger Govier


"Jamie" wrote in message
...
The sheet is part of an automated workbook in which queries bring in
the data
and then it is automatically sorted and run through various
calculations, the
list size will change as changes are made in the database, for the
most part
this will be a hands off spreadsheet controlled through a scheduler,
the only
thing the person using the spreadsheet would change is on the first
page I
have set up cells that are tied into lookups so they can change how
lists are
sorted or searched. I hope this helps

"Teethless mama" wrote:

When you said "I can't use the filter to do it". Do you mean, you are
not
allowed to use it or you don't want to use it or it doesn't works
when you
use it

To me, the easiest way is to use AutoFilter.

"Jamie" wrote:

Hello

I have list that i need to sort and a I can't use the filter to do
it, I
want to use formulas to do it. What i want to do is use a formula
to take all
the rows that have Table No "1" and display them starting in say
column G. I
have been trying offsets, and lookups but I struggling with this. I
can't get
the filter to work the way I want either. Any help would be
appreciated

A B C D E

1 Table No. H Q Start Date End Date
2 1 2.5 25 2006-05-14 2005-06-14
3 1 3.5 35 2006-05-14 2005-06-14
4 2 3.0 30 2006-10-14 2005-12-14
4 2 4.0 40 2006-10-14 2005-12-14

In G2=1

H2
=IF(ISERR(SMALL(IF($A$2:$A$5=$G$2,ROW(INDIRECT("1: "&ROWS($A$2:$A$5)))),ROWS($1:1))),"",INDEX($B$2:$B $5,SMALL(IF($A$2:$A$5=$G$2,ROW(INDIRECT("1:"&ROWS( $A$2:$A$5)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
adjust to suit
Copy across then change the Index range, and copy down as far as needed



"Jamie" wrote:

Hello

I have list that i need to sort and a I can't use the filter to do it, I
want to use formulas to do it. What i want to do is use a formula to take all
the rows that have Table No "1" and display them starting in say column G. I
have been trying offsets, and lookups but I struggling with this. I can't get
the filter to work the way I want either. Any help would be appreciated

A B C D E

1 Table No. H Q Start Date End Date
2 1 2.5 25 2006-05-14 2005-06-14
3 1 3.5 35 2006-05-14 2005-06-14
4 2 3.0 30 2006-10-14 2005-12-14
4 2 4.0 40 2006-10-14 2005-12-14