Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Named Ranges
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Named Ranges
set the source for the validation to =INDIRECT(B1) Or whatever the address or
name of the relevant cell might be. Cheers, Pete. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Named Ranges
On Fri, 30 Oct 2009 05:24:01 -0700, Pete McCosh
wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Dynamic Named Ranges
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. On Fri, 30 Oct 2009 20:12:26 -0700, Archimedes' Lever wrote: On Fri, 30 Oct 2009 05:24:01 -0700, Pete McCosh wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
can VLOOKUP use dynamic named ranges? | Excel Discussion (Misc queries) | |||
Dynamic Named Ranges Viewing in Name Box | Excel Worksheet Functions | |||
Dynamic Named Ranges | Excel Discussion (Misc queries) | |||
Dynamic Named Ranges | Charts and Charting in Excel | |||
Dynamic Named Ranges | Excel Discussion (Misc queries) |