C
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.
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.