View Single Post
  #7  
Old April 16th, 2010, 12:26 PM posted to microsoft.public.excel.worksheet.functions
UKMAN
external usenet poster
 
Posts: 70
Default copying a formula down wards

Mr T,

many thanks and it worked perfectly.

To all others many thanks as well for your help.

Regards

UKMAN1

"T. Valko" wrote:

Try this...

Enter this formula in A1. This will return the count of records that meet
the criteria.

=COUNTIF(F$7:F$198,AQ$6)

Enter this array formula** in B1 and copy down until you get 0s.. This will
extract the records that meet the criteria.

=IF(ROWS(B$1:B1)A$1,0,INDEX(Proj_code,SMALL(IF(F$ 7:F$198=AQ$6,ROW(Proj_code)),ROWS(B$1:B1))-MIN(ROW(Proj_code))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"UKMAN" wrote in message
...
=INDEX(Proj_code,MATCH(AQ$6,F$7:F$198,0))

the above formula works in that it shows the first match.

As there could be mulitpule records what do I need to do to so when I copy
it down it will bring the next match or 0 if no further matches in the
table
it is searching?

Many thanks

UKMAN1



.