A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

copying a formula down wards



 
 
Thread Tools Display Modes
  #1  
Old April 15th, 2010, 05:08 PM posted to microsoft.public.excel.worksheet.functions
UKMAN
external usenet poster
 
Posts: 70
Default copying a formula down wards

=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
  #2  
Old April 15th, 2010, 06:02 PM posted to microsoft.public.excel.worksheet.functions
Mike H
external usenet poster
 
Posts: 8,419
Default copying a formula down wards

Hi,

Try this ARRAY formula. ARRAY enter it and it will return the first match,
drag down for the second etc. It will return an error if there isn't a second
match so you could wrap the whole thing =isserror(formula etc

=OFFSET(IF(ROWS(B$7:B7)=COUNTIF(Proj_code,$E$1),I NDEX($F$7:$F$198,SMALL(IF(Proj_code=$AQ$6,ROW(Proj _code)-ROW($E$1)+1),ROWS(B$7:B7))),""),-6,0)

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"UKMAN" wrote:

=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

  #3  
Old April 15th, 2010, 06:21 PM posted to microsoft.public.excel.worksheet.functions
Chip Pearson
external usenet poster
 
Posts: 1,343
Default copying a formula down wards

Select the range of cells in which you want the results, say L18:L25.
Assuming you have numeric values in D411 and the corresponding
values to return in E4:E11, enter the following array formula and
press CTRL SHIFT ENTER. You must use CTRL SHIFT ENTER to enter the
formula into an array of cells, rather than a single cell.

=IF(ROW()-ROW(L$18)COUNTIF(D411,"b"),LARGE(IF(D411="b", E4:E11,0),ROW(INDIRECT("1:"&COUNTIF(D411,"b")))) ,0)

Change the reference to L$18 to the first cell in the results range
that contains the formula. Change the "b" to the value you want to
look up in D411.

This is an array formula, so you MUST press CTRL SHIFT ENTER rather
than just ENTER when you first enter the formula and whenever you edit
it later. If you do this correctly, Excel will display the formula in
the formula bar enclosed in curly braces { }. You don't type in the
braces; Excel puts them there automatically. The formula will not work
correctly if you do not enter it with CTRL SHIFT ENTER. See
www.cpearson.com/Excel/ArrayFormulas.aspx for much more information
about array formulas.



Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com




On Thu, 15 Apr 2010 09:08:01 -0700, UKMAN
wrote:

=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

  #4  
Old April 15th, 2010, 07:55 PM posted to microsoft.public.excel.worksheet.functions
UKMAN
external usenet poster
 
Posts: 70
Default copying a formula down wards

Chip, thanks for the reply but

below is the formula and my changes but it says too many arquements?? excel
hights the first ",0".

For clarity in cell ref in the hope I have done the correct changes:

AN$9 is the first line/cell for the report results
f7:f198 is the range of value in AQ6 (both text)is to match
b7:b198 is where the value (i.e. PC01) to be return to an9

IF(ROW()-ROW(AN$9)COUNTIF(F$7:F$198,$AQ$6),LARGE(IF(F$7:F$ 198,$AQ$6,B$7:B$198,0),ROW(INDIRECT("1:"&COUNTIF(F $7:F$198,$AQ$6)))),0)

I do thank you for your help as I am trying to understand the nore indepth
formulas

UKMAN1


"Chip Pearson" wrote:

Select the range of cells in which you want the results, say L18:L25.
Assuming you have numeric values in D411 and the corresponding
values to return in E4:E11, enter the following array formula and
press CTRL SHIFT ENTER. You must use CTRL SHIFT ENTER to enter the
formula into an array of cells, rather than a single cell.

=IF(ROW()-ROW(L$18)COUNTIF(D411,"b"),LARGE(IF(D411="b", E4:E11,0),ROW(INDIRECT("1:"&COUNTIF(D411,"b")))) ,0)

Change the reference to L$18 to the first cell in the results range
that contains the formula. Change the "b" to the value you want to
look up in D411.

This is an array formula, so you MUST press CTRL SHIFT ENTER rather
than just ENTER when you first enter the formula and whenever you edit
it later. If you do this correctly, Excel will display the formula in
the formula bar enclosed in curly braces { }. You don't type in the
braces; Excel puts them there automatically. The formula will not work
correctly if you do not enter it with CTRL SHIFT ENTER. See
www.cpearson.com/Excel/ArrayFormulas.aspx for much more information
about array formulas.



Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com




On Thu, 15 Apr 2010 09:08:01 -0700, UKMAN
wrote:

=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

.

  #5  
Old April 15th, 2010, 11:02 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default copying a formula down wards

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



  #6  
Old April 16th, 2010, 11:43 AM posted to microsoft.public.excel.worksheet.functions
Steve Dunn
external usenet poster
 
Posts: 192
Default copying a formula down wards

Too many arguments is referring to this part of your formula:

IF(F$7:F$198, $AQ$6, B$7:B$198, 0)

because an IF function only requires 3 arguments. I suspect you meant:

IF(F$7:F$198=$AQ$6,B$7:B$198,0)



"UKMAN" wrote in message
...
Chip, thanks for the reply but

below is the formula and my changes but it says too many arquements??
excel
hights the first ",0".

For clarity in cell ref in the hope I have done the correct changes:

AN$9 is the first line/cell for the report results
f7:f198 is the range of value in AQ6 (both text)is to match
b7:b198 is where the value (i.e. PC01) to be return to an9

IF(ROW()-ROW(AN$9)COUNTIF(F$7:F$198,$AQ$6),LARGE(IF(F$7:F$ 198,$AQ$6,B$7:B$198,0),ROW(INDIRECT("1:"&COUNTIF(F $7:F$198,$AQ$6)))),0)

I do thank you for your help as I am trying to understand the nore indepth
formulas

UKMAN1


"Chip Pearson" wrote:

Select the range of cells in which you want the results, say L18:L25.
Assuming you have numeric values in D411 and the corresponding
values to return in E4:E11, enter the following array formula and
press CTRL SHIFT ENTER. You must use CTRL SHIFT ENTER to enter the
formula into an array of cells, rather than a single cell.

=IF(ROW()-ROW(L$18)COUNTIF(D411,"b"),LARGE(IF(D411="b", E4:E11,0),ROW(INDIRECT("1:"&COUNTIF(D411,"b")))) ,0)

Change the reference to L$18 to the first cell in the results range
that contains the formula. Change the "b" to the value you want to
look up in D411.

This is an array formula, so you MUST press CTRL SHIFT ENTER rather
than just ENTER when you first enter the formula and whenever you edit
it later. If you do this correctly, Excel will display the formula in
the formula bar enclosed in curly braces { }. You don't type in the
braces; Excel puts them there automatically. The formula will not work
correctly if you do not enter it with CTRL SHIFT ENTER. See
www.cpearson.com/Excel/ArrayFormulas.aspx for much more information
about array formulas.



Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com




On Thu, 15 Apr 2010 09:08:01 -0700, UKMAN
wrote:

=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

.


  #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



.

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

Steve,

many thanks for your help.

UKMAN1

"Steve Dunn" wrote:

Too many arguments is referring to this part of your formula:

IF(F$7:F$198, $AQ$6, B$7:B$198, 0)

because an IF function only requires 3 arguments. I suspect you meant:

IF(F$7:F$198=$AQ$6,B$7:B$198,0)



"UKMAN" wrote in message
...
Chip, thanks for the reply but

below is the formula and my changes but it says too many arquements??
excel
hights the first ",0".

For clarity in cell ref in the hope I have done the correct changes:

AN$9 is the first line/cell for the report results
f7:f198 is the range of value in AQ6 (both text)is to match
b7:b198 is where the value (i.e. PC01) to be return to an9

IF(ROW()-ROW(AN$9)COUNTIF(F$7:F$198,$AQ$6),LARGE(IF(F$7:F$ 198,$AQ$6,B$7:B$198,0),ROW(INDIRECT("1:"&COUNTIF(F $7:F$198,$AQ$6)))),0)

I do thank you for your help as I am trying to understand the nore indepth
formulas

UKMAN1


"Chip Pearson" wrote:

Select the range of cells in which you want the results, say L18:L25.
Assuming you have numeric values in D411 and the corresponding
values to return in E4:E11, enter the following array formula and
press CTRL SHIFT ENTER. You must use CTRL SHIFT ENTER to enter the
formula into an array of cells, rather than a single cell.

=IF(ROW()-ROW(L$18)COUNTIF(D411,"b"),LARGE(IF(D411="b", E4:E11,0),ROW(INDIRECT("1:"&COUNTIF(D411,"b")))) ,0)

Change the reference to L$18 to the first cell in the results range
that contains the formula. Change the "b" to the value you want to
look up in D411.

This is an array formula, so you MUST press CTRL SHIFT ENTER rather
than just ENTER when you first enter the formula and whenever you edit
it later. If you do this correctly, Excel will display the formula in
the formula bar enclosed in curly braces { }. You don't type in the
braces; Excel puts them there automatically. The formula will not work
correctly if you do not enter it with CTRL SHIFT ENTER. See
www.cpearson.com/Excel/ArrayFormulas.aspx for much more information
about array formulas.



Cordially,
Chip Pearson
Microsoft Most Valuable Professional,
Excel, 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com




On Thu, 15 Apr 2010 09:08:01 -0700, UKMAN
wrote:

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


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

Mike,
many thanks for your help.

UKMAN1

"Mike H" wrote:

Hi,

Try this ARRAY formula. ARRAY enter it and it will return the first match,
drag down for the second etc. It will return an error if there isn't a second
match so you could wrap the whole thing =isserror(formula etc

=OFFSET(IF(ROWS(B$7:B7)=COUNTIF(Proj_code,$E$1),I NDEX($F$7:$F$198,SMALL(IF(Proj_code=$AQ$6,ROW(Proj _code)-ROW($E$1)+1),ROWS(B$7:B7))),""),-6,0)

This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"UKMAN" wrote:

=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

  #10  
Old April 16th, 2010, 03:36 PM posted to microsoft.public.excel.worksheet.functions
T. Valko
external usenet poster
 
Posts: 15,759
Default copying a formula down wards

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"UKMAN" wrote in message
...
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



.



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:31 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.