hi everyone.
I have some records with different data.
Now let's assume that as time goes by, some of these should be
considered in my analysis and some not. And this is something that
might change again.
So I put a format check that if selected makes that record "Active". I
use it for all my sums and calculations.
Now, what if -in another worksheet or another section of the same
worksheet- I want to create a list of the active records only?
First thing that came to my mind, use conditional formatting. Mmmmmm

Second thought: nested IF. Something like: if first record is not
active, then check the second, then the third, if active: copy here
But I thought this would create a problem in the second line below,
because besides the nested If I should also use some instruction
preventing copying twice the same active record. So I thing it would
be a mess.

Basically, what I'd like to have is a dynamic list of the active
records, something changing when I activate/deactivate my records.
Is that possible?
hope my question is clear

thanks a lot in advance

[mike-strawberry]

Hi Domanda, perhaps the following is in the right direction for you

http://www.contextures.com/xladvfilter01.html#ExtractWs

Regards, Mike

On Apr 16, 9:16*pm, Domanda <D...@anda.net> wrote:
> hi everyone.
> I have some records with different data.
> Now let's assume that as time goes by, some of these should be
> considered in my analysis and some not. And this is something that
> might change again.
> So I put a format check that if selected makes that record "Active". I
> use it for all my sums and calculations.
> Now, what if -in another worksheet or another section of the same
> worksheet- I want to create a list of the active records only?
> First thing that came to my mind, use conditional formatting. Mmmmmm
>
> Second thought: nested IF. Something like: if first record is not
> active, then check the second, then the third, if active: copy here
> But I thought this would create a problem in the second line below,
> because besides the nested If I should also use some instruction
> preventing copying twice the same active record. So I thing it would
> be a mess.
>
> Basically, what I'd like to have is a dynamic list of the active
> records, something changing when I activate/deactivate my records.
> Is that possible?
> hope my question is clear
>
> thanks a lot in advance

Here's a start, assuming that you don't want a make a macro. Number
your records from 1 to 9999 say starting from A2. In column B you put
an "a" for those active records. The records occupy columns C,
D .... The idea is to arrange for somewhere like column H to have a
list of those numbers in col A which have an "a" in col B. Once you
have those, you can copy the records over to cols I, J, .. using
VLOOKUP or OFFSET.

H1 is empty. In H2 put =H1+MATCH("a",OFFSET($A$1,H1+1,1,9999,1),0)
Copy down. The record numbers should appear.

I like OFFSET myself to copy the records. If the data is three
columns in C, D and E, hightlight I2:J2:K2 and type the array formula
using =OFFSET($A$1,H2,2,1,3) and enter using Ctrl+Shift+Enter

Pity about the #N/A's when you run out of records. Someone might
think of some ingenious way round this.

xt

On Mon, 18 Apr 2011 21:16:05 -0700 (PDT), Xt
<(E-Mail Removed)> wrote:

e records over to cols I, J, .. using
>VLOOKUP or OFFSET.
>
>H1 is empty. In H2 put =H1+MATCH("a",OFFSET($A$1,H1+1,1,9999,1),0)
>Copy down. The record numbers should appear.

>Pity about the #N/A's when you run out of records. Someone might
>think of some ingenious way round this.
>
>xt
>


thanks a lot

why "=H1" if you wrote H1 is empty?

and #N/A is indeed what I wanted to avoid..

thanks a lot anyhow!

On Apr 19, 6:00*pm, Domanda <D...@anda.net> wrote:
> On Mon, 18 Apr 2011 21:16:05 -0700 (PDT), Xt
>
> <the.christ...@clear.net.nz> wrote:
>
> * e records over to cols I, J, .. using
>
> >VLOOKUP or OFFSET.
>
> >H1 is empty. *In H2 put =H1+MATCH("a",OFFSET($A$1,H1+1,1,9999,1),0)
> >Copy down. *The record numbers should appear.
> >Pity about the #N/A's when you run out of records. *Someone might
> >think of some ingenious way round this.
>
> >xt
>
> thanks a lot
>
> why "=H1" if you wrote H1 is empty?
>
> and #N/A is indeed what I wanted to avoid..
>
> thanks a lot anyhow!

H1 can be 0, but not a heading. In my version 2007 it's not this
which causes the #N/A but lower down at the bottom.

=IF(ISNA(H2),"",OFFSET($A$1,H2,2,1,3)) as an array fixes things
somewhat.