PDA

View Full Version : Autoupdate Dropbox Values



mike112
06-04-2012, 07:47 AM
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! : pray2:

Bob Phillips
06-04-2012, 08:05 AM
Use a defined range name for the items and reference that in the dropdown (PS DropBox is a file sharing facility :))

mike112
06-04-2012, 08:23 AM
Use a defined range name for the items and reference that in the dropdown (PS DropBox is a file sharing facility :))

I meant a drop down list haha :rotlaugh: . (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.)

Bob Phillips
06-04-2012, 09:29 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

mike112
06-04-2012, 11:28 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

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.

mike112
06-05-2012, 07:04 AM
Bump.. Still looking to see if anyone have any information on whether this is possible. Thanks!

Bob Phillips
06-05-2012, 07:27 AM
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.

mike112
06-05-2012, 07:37 AM
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.

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)

mike112
06-07-2012, 09:13 AM
Take it this isn't possible?