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
.
|