software

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

=IF(ROWS(C$2:C2)lt;=$B$2,INDEX(*$F$2:$F$8*,SMALL(IF( COUNTIF(INDIRECT(quot;'quot;amp;*$F$2:$F$8*amp;quot;'!B2quot;),quot;gt;quot;amp;$A$2) ,IF(COUNTIF(INDIRECT(quot;'quot;amp;*$F$2:$F$8*amp;quot;'!B2quot;),quot;lt;=quot;amp; $A$3),ROW(*$F$2:$F$8*)-ROW($F$2) 1)),ROWS($C$2:C2))),quot;quot;)

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: www.excelforum.com/member.php...oamp;userid=33986
View this thread: www.excelforum.com/showthread...hreadid=538328Look in HELP for the INDIRECT() function.
Even better: use a Defined Name (Insertgt;Namegt;Define)

--
Kind regards,

Niek Ottenquot;fastballfreddyquot; lt;fastballfreddy.277yxm_1146639302.3065@excelforu m-nospam.comgt; wrote in message
news:fastballfreddy.277yxm_1146639302.3065@excelfo rum-nospam.com...
|
| I have 7 people in cells F2 to F8. Here's the formula:
|
|
=IF(ROWS(C$2:C2)lt;=$B$2,INDEX(*$F$2:$F$8*,SMALL(IF( COUNTIF(INDIRECT(quot;'quot;amp;*$F$2:$F$8*amp;quot;'!B2quot;),quot;gt;quot;amp;$A$2) ,IF(COUNTIF(INDIRECT(quot;'quot;amp;*$F$2:$F$8*amp;quot;'!B2quot;),quot;lt;=quot;amp; $A$3),ROW(*$F$2:$F$8*)-ROW($F$2) 1)),ROWS($C$2:C2))),quot;quot;)
|
| 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: www.excelforum.com/member.php...oamp;userid=33986
| View this thread: www.excelforum.com/showthread...hreadid=538328
|

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
------------------------------------------------------------------------
starguy's Profile: www.excelforum.com/member.php...oamp;userid=32434
View this thread: www.excelforum.com/showthread...hreadid=538328
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.--
starguy
------------------------------------------------------------------------
starguy's Profile: www.excelforum.com/member.php...oamp;userid=32434
View this thread: www.excelforum.com/showthread...hreadid=538328