Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula help
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula help
"Anthony" wrote...
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula help
Yikes, sounds very technical,I'll have a go but fear the worse!
"Harlan Grove" wrote: "Anthony" wrote... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Hide formula | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Help with macro formula and variable | Excel Worksheet Functions |