Dynamic named range & Offset

I need some help w/ Dynamic named range. First time using them.

Here's the formula that I have now w/ the range being AH4:AH9.

=IF(ROWS(B$5:B5)<=$B$4,INDEX($AH$4:$AH$9,SMALL(IF(COUNTIF(INDIRECT("'"&$AH$4:$AH$9&"'!B9"),">"&$AI$4),IF(COUNTIF(INDIRECT("'"&$AH$4:$AH$9&"'!B9"),"<="&$AI$5),ROW($AH$4:$AH$9)-ROW($AH$4)+1)),ROWS(B$5:$B5))),"")

Now I'm not sure if this is how I'm suppose to do it, but let's say I want range AH4:AH30. So I highlight that and click insert, name, define and name it - Employees. Do I have to change the "Refers to" formula or keep it as is? Do I need to use an Offset formula there and if so what would that be? Finally after that is all figured out, do I change $AH$4:$AH$9 to Employees?

Just to let you know the purpose, I have 6 employees, but want my formula to change if I add employees or subtract some. The formula is used for a full year, so 365 times and I want it to update automatically if I add employees.

Any help appreciated. Am I anywhere close? Thanks.

You want to set the RefersTo for employees as

=OFFSET($AH$4,,,COUNTA($A$H4:$AH$3000),1))

and change the formula to

=IF(ROWS(B$5:B5)<=$B$4,INDEX(Employees,
SMALL(IF(COUNTIF(INDIRECT("'"&Employees&"'!B9"),">"&$AI$4),
IF(COUNTIF(INDIRECT("'"&Employees&"'!B9"),"<="&$AI$5),ROW(Employees)-ROW(Emp
loyees)+1)),
ROWS(B$5:$B5))),"")

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"fastballfreddy"
<fastballfreddy.279p7a_1146720001.3908@excelforum-nospam.com> wrote in
message news:fastballfreddy.279p7a_1146720001.3908@excelforum-nospam.com...
>
> I need some help w/ Dynamic named range. First time using them.
>
> Here's the formula that I have now w/ the range being AH4:AH9.
>
>
=IF(ROWS(B$5:B5)<=$B$4,INDEX(*$AH$4:$AH$9*,SMALL(IF(COUNTIF(INDIRECT("'"&*$A
H$4:$AH$9*&"'!B9"),">"&$AI$4),IF(COUNTIF(INDIRECT("'"&*$AH$4:$AH$9*&"'!B9"),
"<="&$AI$5),ROW(*$AH$4:$AH$*9)-ROW($AH$4)+1)),ROWS(B$5:$B5))),"")
>
> Now I'm not sure if this is how I'm suppose to do it, but let's say I
> want range AH4:AH30. So I highlight that and click insert, name,
> define and name it - Employees. Do I have to change the "Refers to"
> formula or keep it as is? Do I need to use an Offset formula there and
> if so what would that be? Finally after that is all figured out, do I
> change *$AH$4:$AH$9* to Employees?
>
> Just to let you know the purpose, I have 6 employees, but want my
> formula to change if I add employees or subtract some. The formula is
> used for a full year, so 365 times and I want it to update
> automatically if I add employees.
>
> Any help appreciated. Am I anywhere close? Thanks.
>
>
> --
> fastballfreddy
> ------------------------------------------------------------------------
> fastballfreddy's Profile:
http://www.excelforum.com/member.php...o&userid=33986
> View this thread: http://www.excelforum.com/showthread...hreadid=538755
>