Dynamic Named Ranges

[CellShocked]

I have a named range from my "Info" worksheet. On my "Tracker"
worksheet, I have drop down validation lists which refer to the named
range "Hours".

The drop down list works fine. It refers to data on another worksheet,
which drop down validation lists do not allow. It allows it because it
is a named range.

So, my problem is that I want to dynamically change the range that name
refers to based on a selection on my Info worksheet.

I select a number from a drop down list for time increments.

Those increments are listed 30, 20, 15, 10, 5, 2

That selection points to a lookup a few cells over that changes the
value contained in yet another cell. The value contained in that cell is
the data that defines each range. I want to dynamically alter a named
range by using the value in a cell to define the range.

So, I tried to reference the cell directly, which the drop down
validation rules do not allow (references to other worksheet data)

So, it DOES allow such references when they are tied to a named range,
so I want to change that named range as changing the validation rule on
the fly is not possible.

Placing the lists on the tracker worksheet is not an option.

I figured that one way to do this would be to dynamically change the
data which resides in the named range space I currently use with success.

I could use lookups that refer to the increment selection made to fill
in the range I have named.

That way, I do not need to change the named range on the fly, only the
data that resides within that named cell range.

Which is the best option, and if dynamically changing the named range
values is the best option, how do I do it.

If changing the cell values is the best option, say so, because I can
do that one, I just wanted to see here if you guys know of a way to
change a named range declaration on the fly without any VB.

 

[Pete McCosh]

set the source for the validation to =INDIRECT(B1) Or whatever the address or
name of the relevant cell might be.

Cheers, Pete.

 

[Archimedes' Lever]

set the source for the validation to =INDIRECT(B1) Or whatever the address or
name of the relevant cell might be.

Cheers, Pete.

I have. It fails.

The relevant cell has contained everything from the desired named
range, to the actual range declarations.

The other issue is that the referenced cell is on another sheet, which
I explained. The validation pukes on that attempt.

Shame that you are the only one that answered. Interesting how folks
that are otherwise very smart glance at a thread and think a response
means that it got answered. Whether they check that the response was
accurate or not probably doesn't happen.

I guess only certain brains do thing automatically. Like using your
turn signal on a car. The dopes that do not always have a reason why
they think it is unimportant. I do it automatically.

When I look at a thread here, I look ate the responses to SEE if the
goal was met.

So no, as simple as it seems, your reply is not the solution... if
there is one.

I think I need to dynamically change the data contained in the one
named range it does accept. I cannot change that validation criteria on
the fly, but I can change what that named range contains on the fly,
based on my increment selection.

Thanks for your help, however.

 

[CellShocked]

I succeeded by allowing my selection list modify a lookup list formula
string so that the list fills out with the new data. The list was given
a name for the range it covers. It works perfectly.

So, as far as dynamically changing the value of a named range, it is
still up in the air.
Instead, I chose to dynamically change the data that a named range
refers to.