Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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(I F(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. -- fastballfreddy ------------------------------------------------------------------------ fastballfreddy's Profile: http://www.excelforum.com/member.php...o&userid=33986 View this thread: http://www.excelforum.com/showthread...hreadid=538755 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic named range & Offset
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@excelforu m-nospam.com wrote in message news:fastballfreddy.279p7a_1146720001.3908@excelfo rum-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(I F(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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookup Error in Part of a Named Range | Excel Worksheet Functions | |||
Offset Function works in cell, not in named range | Excel Worksheet Functions | |||
Cannot Expand Named Range - when size of the Range exceeds | Excel Discussion (Misc queries) | |||
Using a formula to create named range reference | Excel Worksheet Functions | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |