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

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

--
Kind regards,

Niek Otten


"fastballfreddy" <fastballfreddy.277yxm_1146639302.3065@excelforu m-nospam.com 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)<=$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
|

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: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://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: http://www.excelforum.com/member.php...o&userid=32434
View this thread: http://www.excelforum.com/showthread...hreadid=538328