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

On Apr 16, 9:16*pm, Domanda 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
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 wrote:
On Mon, 18 Apr 2011 21:16:05 -0700 (PDT), Xt

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.