software

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,quot;MYYYYquot;),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

quot;Anthonyquot; gt; wrote...
gt;I am looking for a formula that will seach A1:A400 in my 'stats' worksheet
gt;for a date which is shown in cell A1 of my 'chart' worksheet. Once this
gt;date
gt;is found copy/paste the corresponding data which is found in S1:S400 in
gt;'stats' and keep doing this until there are no more cells found in range
gt;A1:A400 with this date.
gt;
gt;Now, being a little bit of a novice here I tried
gt;
gt;=COPYIF(STATS!A1:A400,--TEXT(S1,quot;MYYYYquot;),STATS!S1:S400
gt;
gt;and to no surpirse I had a #NAME error, as I assume there is no COPYIF
gt;function in Excel.
gt;
gt;Can anybody suggest a better formula or do I have to use VBA to get the
gt;data
gt;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 gt; Filter gt;
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)gt;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))),quot;quot;)

and fill X2 down as far as you think it'd be needed. Down through X400 would
be safest.
Yikes, sounds very technical,I'll have a go but fear the worse!

quot;Harlan Grovequot; wrote:

gt; quot;Anthonyquot; gt; wrote...
gt; gt;I am looking for a formula that will seach A1:A400 in my 'stats' worksheet
gt; gt;for a date which is shown in cell A1 of my 'chart' worksheet. Once this
gt; gt;date
gt; gt;is found copy/paste the corresponding data which is found in S1:S400 in
gt; gt;'stats' and keep doing this until there are no more cells found in range
gt; gt;A1:A400 with this date.
gt; gt;
gt; gt;Now, being a little bit of a novice here I tried
gt; gt;
gt; gt;=COPYIF(STATS!A1:A400,--TEXT(S1,quot;MYYYYquot;),STATS!S1:S400
gt; gt;
gt; gt;and to no surpirse I had a #NAME error, as I assume there is no COPYIF
gt; gt;function in Excel.
gt; gt;
gt; gt;Can anybody suggest a better formula or do I have to use VBA to get the
gt; gt;data
gt; gt;I require (yikes!)
gt;
gt; VBA isn't necessary if you could live with some redundancy.
gt;
gt; First, the easiest way to do this manually would be using Data gt; Filter gt;
gt; AutoFilter *IF* you add an extra row at the top of your data. Then select
gt; the date desired in the col A drop down, and copy the resulting col S values
gt; and paste them where you want them.
gt;
gt; With redundant formulas,
gt;
gt; X1:
gt; =VLOOKUP(S1,stats!$A$1:$S$400,19,0)
gt;
gt; X2 [array formula]:
gt; =IF(COUNTIF(stats!$A$1:$S$400,S$1)gt;COUNTA(X$1:X1),
gt; INDEX(stats!$S$1:$S$400,SMALL(IF(stats!$A$1:$A$400 =S$1,
gt; ROW(stats!$A$1:$A$400)),ROWS(X$1:X2))),quot;quot;)
gt;
gt; and fill X2 down as far as you think it'd be needed. Down through X400 would
gt; be safest.
gt;
gt;
gt;