formula help

[Guest]

Hi,

I am looking for a formula that will seach A1:A400 in my 'stats' worksheet
for a date which is shown in cell A1 of my 'chart' worksheet. Once this date
is found copy/paste the corresponding data which is found in S1:S400 in
'stats' and keep doing this until there are no more cells found in range
A1:A400 with this date.

Now, being a little bit of a novice here I tried

=COPYIF(STATS!A1:A400,--TEXT(S1,"MYYYY"),STATS!S1:S400

and to no surpirse I had a #NAME error, as I assume there is no COPYIF
function in Excel.

Can anybody suggest a better formula or do I have to use VBA to get the data
I require (yikes!)
Many thanks
Anthony

 

[Harlan Grove]

I am looking for a formula that will seach A1:A400 in my 'stats' worksheet
for a date which is shown in cell A1 of my 'chart' worksheet. Once this
date
is found copy/paste the corresponding data which is found in S1:S400 in
'stats' and keep doing this until there are no more cells found in range
A1:A400 with this date.

Now, being a little bit of a novice here I tried

=COPYIF(STATS!A1:A400,--TEXT(S1,"MYYYY"),STATS!S1:S400

and to no surpirse I had a #NAME error, as I assume there is no COPYIF
function in Excel.

Can anybody suggest a better formula or do I have to use VBA to get the
data
I require (yikes!)

VBA isn't necessary if you could live with some redundancy.

First, the easiest way to do this manually would be using Data > Filter >
AutoFilter *IF* you add an extra row at the top of your data. Then select
the date desired in the col A drop down, and copy the resulting col S values
and paste them where you want them.

With redundant formulas,

X1:
=VLOOKUP(S1,stats!$A$1:$S$400,19,0)

X2 [array formula]:
=IF(COUNTIF(stats!$A$1:$S$400,S$1)>COUNTA(X$1:X1),
INDEX(stats!$S$1:$S$400,SMALL(IF(stats!$A$1:$A$400=S$1,
ROW(stats!$A$1:$A$400)),ROWS(X$1:X2))),"")

and fill X2 down as far as you think it'd be needed. Down through X400 would
be safest.

 

[Guest]

Yikes, sounds very technical,I'll have a go but fear the worse!

I am looking for a formula that will seach A1:A400 in my 'stats' worksheet
for a date which is shown in cell A1 of my 'chart' worksheet. Once this
date
is found copy/paste the corresponding data which is found in S1:S400 in
'stats' and keep doing this until there are no more cells found in range
A1:A400 with this date.

Now, being a little bit of a novice here I tried

=COPYIF(STATS!A1:A400,--TEXT(S1,"MYYYY"),STATS!S1:S400

and to no surpirse I had a #NAME error, as I assume there is no COPYIF
function in Excel.

Can anybody suggest a better formula or do I have to use VBA to get the
data
I require (yikes!)

VBA isn't necessary if you could live with some redundancy.

First, the easiest way to do this manually would be using Data > Filter >
AutoFilter *IF* you add an extra row at the top of your data. Then select
the date desired in the col A drop down, and copy the resulting col S values
and paste them where you want them.

With redundant formulas,

X1:
=VLOOKUP(S1,stats!$A$1:$S$400,19,0)

X2 [array formula]:
=IF(COUNTIF(stats!$A$1:$S$400,S$1)>COUNTA(X$1:X1),
INDEX(stats!$S$1:$S$400,SMALL(IF(stats!$A$1:$A$400=S$1,
ROW(stats!$A$1:$A$400)),ROWS(X$1:X2))),"")

and fill X2 down as far as you think it'd be needed. Down through X400 would
be safest.