software

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)lt;=$B$4,INDEX(*$AH$4:$AH$9*,SMALL(I F(COUNTIF(INDIRECT(quot;'quot;amp;*$AH$4:$AH$9*amp;quot;'!B9quot;),quot;gt;quot;amp;$ AI$4),IF(COUNTIF(INDIRECT(quot;'quot;amp;*$AH$4:$AH$9*amp;quot;'!B9quot; ),quot;lt;=quot;amp;$AI$5),ROW(*$AH$4:$AH$*9)-ROW($AH$4) 1)),ROWS(B$5:$B5))),quot;quot;)

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 quot;Refers toquot;
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: www.excelforum.com/member.php...oamp;userid=33986
View this thread: www.excelforum.com/showthread...hreadid=538755You 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)lt;=$B$4,INDEX(Employees,
SMALL(IF(COUNTIF(INDIRECT(quot;'quot;amp;Employeesamp;quot;'!B9quot;),quot;gt; quot;amp;$AI$4),
IF(COUNTIF(INDIRECT(quot;'quot;amp;Employeesamp;quot;'!B9quot;),quot;lt;=quot;amp;$AI $5),ROW(Employees)-ROW(Emp
loyees) 1)),
ROWS(B$5:$B5))),quot;quot;)

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

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