How to make a dynamic named range

[Guest]

Hi everybody,
Can I make a named dynamic range? I am using data validation and made some
lists to work with but the problem is I have to change the range by the name

define command as I use a new list item. I have used the offset function to

create the dynamic list but is there any way to use a named list in that way?

 

[Ron Coderre]

See Debra Dalgleish's instructions:
http://www.contextures.com/xlNames01.html#Dynamic

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

 

[Guest]

You don't even need an OFFSET.

Say in G1 thru G5 we have:

dog
cat
bird
turtle
hamster

and assign the Name pets to this range. If we delete one of the cells, the
range will adjust. However if we add something below hamster, the range will
not adjust.

So in G1 thru G7 enter:

dog
cat
bird
turtle
hamster
spacer

and make the Named Range include G7. If we want to add pets, add them ABOVE
spacer. The range will then adjust.

 

[iliace]

Here's an example I did for entering attendance data; perhaps you can
adopt this technique to your situation. There is a total of 8 hours
per day in our program; however, there are various excuses for taking
off hours. Sickness, doctor visits, etc.

I have a range starting in A6=0 through A14=8. I do not want to have
more than 8 hours total for any given day.

In column starting on I8, I have excused hours. I7 contains =8-
SUM(I8:I13) which is hours in program. Then I create the validation.
For cells I8:I13, the list source:

=IF(I12<1,OFFSET($A$6,0,0,9-(SUM(I$8:I$13)),1),OFFSET($A$6,0,0,MAX(9-
(SUM(I$8:I$13)),I12)))

This is the behavior. Each individual's template starts out with I8-
I13 blank (that's just one day, for simplicity here), and I7 shows 8.
I8:I13 have valid inputs of 0,1,2,3,4,5,6,7,8 (from A6:A14). If you
select 4 in I8, the options are reduced to I8:I13 become 0 to 4; if
you replace it with 2, the options are now 0 through 6; and so forth.
The validation can easily be copied/pasted to create additional days,
because of the way relative references are set up.

Try it out.

 

[Feejo]

How to create a name range in 2007.

Here's an example I did for entering attendance data; perhaps you can
adopt this technique to your situation. There is a total of 8 hours
per day in our program; however, there are various excuses for taking
off hours. Sickness, doctor visits, etc.

I have a range starting in A6=0 through A14=8. I do not want to have
more than 8 hours total for any given day.

In column starting on I8, I have excused hours. I7 contains =8-
SUM(I8:I13) which is hours in program. Then I create the validation.
For cells I8:I13, the list source:

=IF(I12<1,OFFSET($A$6,0,0,9-(SUM(I$8:I$13)),1),OFFSET($A$6,0,0,MAX(9-
(SUM(I$8:I$13)),I12)))

This is the behavior. Each individual's template starts out with I8-
I13 blank (that's just one day, for simplicity here), and I7 shows 8.
I8:I13 have valid inputs of 0,1,2,3,4,5,6,7,8 (from A6:A14). If you
select 4 in I8, the options are reduced to I8:I13 become 0 to 4; if
you replace it with 2, the options are now 0 through 6; and so forth.
The validation can easily be copied/pasted to create additional days,
because of the way relative references are set up.

Try it out.