easy way to change a formula

[fastballfreddy]

I have 7 people in cells F2 to F8. Here's the formula:

=IF(ROWS(C$2:C2)<=$B$2,INDEX($F$2:$F$8,SMALL(IF(COUNTIF(INDIRECT("'"&$F$2:$F$8&"'!B2"),">"&$A$2),IF(COUNTIF(INDIRECT("'"&$F$2:$F$8&"'!B2"),"<="&$A$3),ROW($F$2:$F$8)-ROW($F$2)+1)),ROWS($C$2:C2))),"")

The range: $F$2:$F$8 -- is in the formula 4 times and I am using this formula 365 times for a full year. What if I include 1,2,3 more people, the range would then need to be extended to F9,F10,F11.

Is there an easy way to change each formula, or do I have to go through 365 formulas and change each one?

Thanks for the help. I really do appreciate it.

[starguy]

select the range where formula resides

press Ctrl+F now press tab Replace
in Find What enter your current range
in Replace with enter new range
press Replace All.

[starguy]

select the range where formula resides

press Ctrl+H
in Find What enter your current range
in Replace with enter new range
press Replace All.

[Niek Otten]

Look in HELP for the INDIRECT() function.
Even better: use a Defined Name (Insert>Name>Define)

--
Kind regards,

Niek Otten


"fastballfreddy" <[email protected]> wrote in message
news:[email protected]...
|
| I have 7 people in cells F2 to F8. Here's the formula:
|
|
=IF(ROWS(C$2:C2)<=$B$2,INDEX(*$F$2:$F$8*,SMALL(IF(COUNTIF(INDIRECT("'"&*$F$2:$F$8*&"'!B2"),">"&$A$2),IF(COUNTIF(INDIRECT("'"&*$F$2:$F$8*&"'!B2"),"<="&$A$3),ROW(*$F$2:$F$8*)-ROW($F$2)+1)),ROWS($C$2:C2))),"")
|
| The range: *$F$2:$F$8* -- is in the formula 4 times and I am using this
| formula 365 times for a full year. What if I include 1,2,3 more people,
| the range would then need to be extended to F9,F10,F11.
|
| Is there an easy way to change each formula, or do I have to go through
| 365 formulas and change each one?
|
| Thanks for the help. I really do appreciate it.
|
|
| --
| fastballfreddy
| ------------------------------------------------------------------------
| fastballfreddy's Profile: http://www.excelforum.com/member.php...o&userid=33986
| View this thread: http://www.excelforum.com/showthread...hreadid=538328
|