Hi guys,
Probably a basic problem, but I currently have a dropdown that contains a list off of which contents are on Sheet1. The problem I'm having is if I have an item selected on the drop menu and change the data on Sheet1, the selected item from the menu is still what it used to be.
Hopefully there's a quick and easy solution to update the data, possible on Worksheet_Change I'm thinking?
Any help would be great!
Last edited by mike112; 06-04-2012 at 08:33 AM.
Use a defined range name for the items and reference that in the dropdown (PS DropBox is a file sharing facility)
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
I meant a drop down list hahaOriginally Posted by xld
Use a defined range name for the items and reference that in the dropdown (PS DropBox is a file sharing facility. (Validation) And as far as your directions I'm not fully understanding. (I'm basically brand new to VBA/Excel.. I really only know the basics.)
Last edited by mike112; 06-04-2012 at 08:34 AM.
Create a defined name by going into Formulas>NameManager>New, give it a name and a RefersTo of something like
=OFFSET($H$1,0,0,COUNTA($H:$H),1)
assuming that your data starts in H1, and in the DV use the name in the formula like so
=myName
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
I've tried it and haven't had any luck. I'll upload a part of my workbook to show you what's going on. The drop downs are on Template, while the data it's looking at is on Design Conditions. (rows Q on both) If you change the data on Design Conditions, the only way for the drop downs to update is to manually reselect the option again.
Bump.. Still looking to see if anyone have any information on whether this is possible. Thanks!
I'm sorry, I just don't know what problem that you are referring to.
Although I would use
=OFFSET('Design Conditions'!$Q$9,0,0,COUNTA('Design Conditions'!$Q:$Q)-1,1)
it works fine as far as I can see.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
You're putting that in the Name Manager, correct? I've tried it there and it's not working. My problem is, for example, if on Template, Q24, you select "Engine", the numbers beside it are set. How ever if I go to Design Template and change the number associated with Engine, it'll change on the list to the right of the table, how ever if you go back to Template, the number is still what it was before I changed it. (Sorry if that's confusing, it's hard to put it into words what I'm trying to do)
Take it this isn't possible?
Posting Permissions
Reply With Quote
