Reset Dependent Drop Down List

May 10, 2007

The only thread I could find on this topic was not resolved. I want to reset the value of a dependent drop down list (in H3) to the first valid choice when F3 is changed.

I have the following code that initially worked but no longer does.


Private Sub Worksheet_Change(ByVal Target As Range)
On Error Goto errHandler
Dim DLRng As Range

If Not Intersect(Target, Me.Range("F3")) Is Nothing Then
If Target.Count > 1 Then Exit Sub
Application.EnableEvents = False
Set DLRng = ActiveWorkbook.Names(Target.Value).RefersToRange
Me.Range("H3").Value = DLRng.Offset(0, 0).Value
End If

exitHandler:
Application.EnableEvents = True
Exit Sub

errHandler:
MsgBox "Could not change dependent cell"
Goto exitHandler
End Sub

I now get the error message ""Could not change dependent cell" every time I pick a different item in the drop down list in cell F3. If I remove the error handler I don't get the error and the code simply doesn't work.

View 9 Replies


ADVERTISEMENT

Dependent Drop Down List With DEPENDENT DEFAULT VALUE

Jan 22, 2010

Situation:
I have two drop down lists 1) Country and 2) States/province

Country has list: [US, Canada]

If "US" is selected, [Arizona ,Florida, NewYork] is listed in the 2nd drop down list
If "Canada" is selected, [Alberta, Ontario, Quebec] is listed in the 2nd drop down list

Scenario:
First, I select "US" and choose the states to "Florida"
Next I change the country to "Canada" and forgot to choose province
Then, the 2nd dropdown list is changed to [Alberta, Ontario, Quebec],
BUT the current value is still "Florida"

Probem:
Now I have "Canada" and "Florida" selected in the sheet

Question:
If the primary list is changed, can I make the 2nd drop down list to show a default value (e.g. blank or the first entry i.e. Alberta)?

View 11 Replies View Related

Dependent Drop Down List Keeping Old Value

Apr 11, 2008

My problem is a dependent drop down list that keeps the previous value when a different value is picked in the first drop down list. The dependent drop down needs to change to empty/blank.

The first drop down list is in cell C3.
The dependant drop down list is in cell C4.
The source in C4: =INDIRECT(VLOOKUP(C3,allnames,2,2))

I had a look at various answers on the internet but somehow cannot get it to work (and that is more of a reflection of my own skills than the writers of those answers!).

View 9 Replies View Related

Create Default For Dependent Drop Down List

Feb 18, 2010

I'm using dependent drop-down lists. In the first box the user can choose a color which drives a set of options in the second box. (e.g. User selects "Red" and in the second dropdown they can then choose "Red A", "Red B", "Red C", etc.). Right now, if you select "Orange", it will still show "Red A" from the previous selection until the user selects the dropdown in the dependent box to refresh the list of options. Is there a way to make it so that second dropdown box will default to "Orange A"?

I tried writing some code to do this, but I can't seem to make it work. I've attached my file.

View 7 Replies View Related

Drop Down List Which Returns Values Based On What Has Been Selected In The Previous Drop Down List In The Adjacent Cell

Mar 19, 2009

I'm trying to create a drop down list which returns values based on what has been selected in the previous drop down list in the adjacent cell, e.g. if 'Apples' is selected in the previous cell then you should only be able to select from 'Gala, Granny Smith', or if 'Oranges' is selected you should only be able to select 'Seville, Blood Orange'. Is there a formula which would do this, or can I use a pivot table somehow? I'm totally stumped.

View 2 Replies View Related

Drop Down List Which Displays A Different Set Of Values Depending Upon The Value Selected By A Previous Drop Down List

Oct 26, 2009

I need to have a drop down list which displays a different set of values depending upon the value selected by a previous drop down list. ie. (drop down box 1)= x, y, z. (drop down box 2)= either x1, x2, x3, or y1, y2, y3, or z1, z2, z3. I can produce a single drop down box thats not a problem but linking several drop down boxes is beyond me .

View 4 Replies View Related

Macros Untick Boxes As Well Reset Drop Down Menus

Jan 17, 2009

I am wondering how to create a macro that will untick boxes as well reset drop down menus.

View 9 Replies View Related

Dependent Drop Down Lists ...

Jul 6, 2009

I have been searching on this for a little while now but have not been able to find a solution to my problem. I have been asked to create a series of dependent dropdown lists using excel so that we can use these for inventory imports into a asset system. The catch on this is that nobody here really has any idea on how to make these dependent lists reference each other correctly. The reason for that is because the fields all have spaces in them and I do not know how to get a list name with a space in it or to create the proper translation for it. An example of this would be something like what I have written in below ....

View 13 Replies View Related

Cell-dependent Drop-down

Oct 12, 2009

Say in A1 i have either Yes or No, in B1 i have a dropdown validation from a list if A1 is Yes and "n/a" if A1 is no.

I thought if i renamed my list validation to "Yes" and labelled "n/a" as "No", then using =INDIRECT(A1) as my list validation would do the trick.

View 2 Replies View Related

Dependent Drop Down Forms

Aug 12, 2007

I am trying to put together two combo boxes, Combo1 and Combo2, the content of Combo2 will depend on the selection made in Combo1, for example Combo1 will have 10 items, once an item is selected Combo2 will have different sub selections relating to that item. I know you can do this via Validation, but the problem with that I have about 4000 options in the combo1 and about 60000 in the second, here is the structure of my data:

First Option /Second Option
Option1_____1.1
Option1_____ 1.2
Option1_____ 1.3
Option2_____ 2.1
Option2_____ 2.2
Option2_____ 2.3
Option3_____ 3.1
Option3_____ 3.2
Option3_____ 3.3

My ultimate final result should look like this, two drop down menus once Option1 is selected second drop down form should display these options:

Option1 /_____ 1.1
____________ 1.2
____________ 1.3

see attachment for complete clarity

View 9 Replies View Related

Validation Drop Down, Dependent Upon Another Cell Value

Apr 21, 2009

I am trying to create validation drop down list for a cell, however I want different drop down lists which are depenent upon another cell.

in Cell A1, I want the options "A", "B","C" etc
If cell A1 = "A", then drop down validation for cell A2 = 1,2,3,4,5
cell A1 = "B", then drop down in cell A2 now equal 6,7,8,9, 10
cell A1 = "C", then drop down in Cell A2 now equal 11,12,13,14,15

View 4 Replies View Related

Semi-dependent Drop Down Lists

Jul 30, 2009

I'm trying to make a number of cells dependent to a drop down list. However, these cells would also be drop down lists and while they'd show a value automatically determined depending on the drop down list, the user would still be able to choose another value.

Aluminium
Coated steel
SS304
SS316

This would be my initial drop down list.

In the same sheet, I could choose the materials for the machines shaft, fasteners, anchors, chains, sprockets, nozzles, etc. These materials would be, again:

Aluminium
Coated steel
SS304
SS316

in 90% of the cases, having the machines frame at SS304 would mean the rest of the materials would also be SS304. However, depending on the needs, the anchors could be SS316. So basically I'd like that the options all be in the same material as the frame, but with the possibility of choosing another material through a drop down list.

View 4 Replies View Related

Dependent/Linked Drop Downs In Cells

Apr 29, 2008

Now he has another task that I am not having any success with. He has asked if we can make an Excel spreadsheet that allows us to access a list of customer units from a drop down menu which is in another workbook, and once you choose that particular unit, another drop down menu will allow only those customers who are assigned to that unit to be selected from that sub-menu. I hope that makes sense.

Say customer A1 (we will call him Delta Co, 2-1 Aviation) is selected from this first drop down menu (which I haven't figured out how to make yet either). Then this selection would access another worksheet in that other workbook with a new drop down menu with only the four customers in that unit. As an example we will use the names SSG Wilson, SGT Jackson, SFC Jones, and SGT Phillips.

So when the Supply Tech here opens the spreadsheet, the first drop down menu, he will select Delta Co, 2-1 Aviation. Then the next menu will only show four names, and he will select SFC Jones.

Once we get this going, I may be able to figure out the other things the commander wants, if not I will be back.

View 9 Replies View Related

Refine Values In Dependent/Linked Drop-Downs

Aug 24, 2009

to make the user experience better by inserting the " Stock Search" sheet to speed up the process. The spreadsheet attached is a simplified version of the current version, where it would normally have many many more sites, areas and models of cars. The existing process means the user has to trawl through the sheet where they are only interested in what sites in their area has stock. I can not change the format of the "Red" and "Yellow" sheets so hoping to insert another sheet to do the work.

I am hoping to have on the "Stock Search" sheet a few drop down boxes.

The user will select

1. Area
2. Type of Colour (the type of colour selected should link with the sheet name in effect, so only stock within the "Red" sheet will be shown if the "Red" colour is selected from the drop down box)
3. Model of Car

This will then give the resulting Store/Stores with stock (value 1 or above) with those conditions met which is showing in the relevant sheet

So as per the attached sheet, if a user selected Area "South West", "Red", "Renault Megane"

then SO10 Clifton 1 and SO15 Yeovil 3 would be shown in a list.

View 5 Replies View Related

Reset Value In Dependent Cell When Another Cell Is Updated

Aug 5, 2006

1. I created a "Top Category Data Dependent Validation List for sub-categories” in the attached spreadsheet. The selection from the Category drop-down list in column B, then drives or limits the choices in the Sub-category drop-down list in column C.

Credits: To achieve the above technique, I used the "Dependent List Validation" document as a technical reference.
the document is available from http://www.ozgrid.com/Excel/dependent-lists.htm

2. If the user updates the Category selection in Column B, then the value in the corresponding row in Column C should be "reset to a ( NULL/Clear) value," as to automatically prevent any human-error in forgetting to also update the Sub-category data in Column C.

(Optional Reading--the following steps are not related to steps 1 & 2 above, just notes regarding other data validation process created in this spreadsheet

3. I also created another column (D) that gets automatically populated with the Category ID,
based on the sub-category drop-down list selection in C.

4. Also in this spreadsheet, a macro checks for any "missing required data
before allowing the end-user to save this worksheet. As an example, In Column A, if there is a "RecordId" that exists in the same row, then the macro checks if the user has entered required values in Column B, C, "AND/OR" D. If the end-user did not enter any of the required values,
then an error message pops-up with this message:

"Cannot Save this file due to Missing Required Data. Please review highlighted record and complete missing data."

Credits:

Special thanks to:
-OzGrid Business Applications for writing code for a UDF (function that returns last word) that I used/modified a little bit to achieve Step 3.

-Carl (member name" carlmack") for his help in the methods I used in Step 4 above.

View 9 Replies View Related

Remove Items From DV List Once Used / Display Elsewhere / Reset List When Filling Across

Sep 13, 2013

It's a quarterly schedule for utility meter readers, divided up into days across the top, and routes/areas down the side.
There's a space with each route for the employee ID to go, depending on who's doing it.

Down the bottom of the sheet, there's a few empty lines for all the available employees who haven't been allocated to a route.

On the second sheet labelled EMP LIST, is all the employee IDs. It's also got the areas they work- it's for something I want to implement later.

I can put all the employees into a single column list, then use basic DV to give a dropdown menu by each route, to assign an employee to it. This is pretty straight forward. I've also been playing with this in combination with a countif, so that employees already assigned do not appear in the list.

This is about as far as I've managed to get with it.

I need to get the employees who aren't assigned to anything to appear in a list below the routes. I'm fairly sure this is just another countif, but I'm not 100% on the exact formulas.

I also need the list in the dropdown menu to reset for each day of the quarter, i.e. if I assign an employee to a route on the 30th day of the quarter, their ID will disappear from the list for any other route for that day, but will still be there for the other days of the quarter.

So far the only way I've managed to do this is by duplicating the employee list for every day of the quarter, which is going to get very cluttered, and also makes staffing changes difficult. I'm hoping there's an easier way to do this using one data set.

I've added an example of the sheet. It's one week and it's only got about 1/3 of the routes we would actually have on any given day.

scheduledemo.xlsx

View 1 Replies View Related

Drop Down List Values Dependant On Value Of Another Drop Down List

Dec 9, 2009

I have a drop down box in column N, however I would like the options in that drop down box to change depending on what appears in column M.

If column M Says "Lapse" then I want one drop down box to appear in column N, If coumn M says "NTU" then I would like column N to show different drop down options.

View 12 Replies View Related

Delete Value Of Dependent List Automatically When Mother-list Gets Manually Deleted?

Feb 8, 2013

I'm currently working with 3 dropdown lists depending on each other using data validation and =indirect(). I arranged them like in a top-down approach where you have a mother-list and child-lists (categories-->subCategories-->subCategoryCriteria). The user should easily chose first a category then a subCategory etc... This part works perfectly

My question is: when a user chose in the dropdown list e.g. a category, a subCategory and a subCategoryCriteria and he now maually deletes the value in the highest level, which is the Category (mother-list), all sub level values like subCategory and subCategoryCriteria will stay. It shouldn't be that way, because it's not logical. If you turn a tree upside-down (top-down approach) and cut one of the higher positioned trunks, all lower positioned trunks will fall off as well, right? So, my question is: can I use a conditional formatting or a formula to delete or fade out the value in the lower lists when the higher list value is being deleted?

I tried to use If-formula in combination with indirect in data validation, like =IF(A1="";("");INDIRECT(B1))...just and example.

The value doesnt need to be deleted, it could also be automatically face out like white on white or something. It's just confusing for the user if he deletes the highest choice and the lower choices still stay.

View 8 Replies View Related

Reset List Values

Nov 22, 2006

I have some list values scattered on work sheet having values [0,1,2,3] how can i reset all list values to 1 by using macro or any other option.

View 5 Replies View Related

Reset Dropdown List To First Entry

Feb 18, 2014

I am trying to reset various (data validation) drop down lists in a the dummy worksheet attached. Most of the code i have come across clears the cells completely, however i would like it to return to the 'select' option (first on the list) in the list.

I have come across this code, but it does not seem to work:

[Code] .........

I want the clearing code to run from the command button in the 'instructions' tab to clear the drop down lists in the '1. Inputs' tab.

Attached File: Reset drop down list.xlsm‎

View 4 Replies View Related

Reset Data Validation List All The Time When Opening File?

Jun 11, 2014

When opening the attached file, for some reason, I have to again select the cells from column A in tab "Transactions" and go to Data Validation and select again LIST - only then the data validation from list resumes working. When I close the file after saving and open it again, the data validation does not work - I have to again go to Data Validation and again reselect LIST (making sure, of course, that I do not mess up Source) to have Data Validation list run again.

what is wrong with list validation when opening the file.xlsx‎

View 4 Replies View Related

Dependent Dropdown List

Mar 20, 2007

I want to create a spreadsheet, where one column allows the user to choose 'P' or 'C' from a list. No problem. I want the next column to contain options specific to what they choose. So if they choose 'C' they get a different list to choose from than if they would have chosen 'P'.

View 11 Replies View Related

List Of Dependent Cells

Apr 24, 2008

Is there a way to quickly create a list of all dependent cells?

View 9 Replies View Related

Validation List To Be Dependent On Another

Mar 8, 2009

I created a spreadsheet with a list of cities and attractions in those cities. I want an attraction drop-down menu (cell validation) to be dependent on the city that’s listed in the cell above it. I used the below formula: =INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($H$1,"-","_"),", ","__"),". ","__")," ","_"))

This works fine on cities with a dash in them (McGhee-Tyson) or just a normal name (Washington, DC). However, I cannot get the attraction drop-down to work if I select a city with a period (St. Louis, MO) or a space (San Antonio, TX). Am I missing something in the formula? I attached a spreadsheet as an example. I use Excel 2007, but saved the spreadsheet in Excel 97 – 2003 format.

View 2 Replies View Related

Creating Dependent Lists On First List?

Dec 4, 2013

I have been having alot of trouble and have read alot. I can't seem to figure out the problem. I am currently working on an excel sheet that will select a name from the first drop down list. Based on that name five other list will be generated just on the first selection.

View 3 Replies View Related

Dropdown List Dependent On IF Function?

Mar 18, 2013

I am trying to have a drop down list which is dependant on the result of a cell.

For example, if cell A1= CAT, then cell A2 should have a 'drop down list 1'; if cell A1=DOG, cell A2 would have 'drop down list 2'; if cell A1=RABBIT, cell A2 would have 'drop down list 3' etc.

Creating the drop down list is easy, I just want to know if it's possible to have different drop down lists depending on the result of a cell.

View 1 Replies View Related

Create Dependent Dropdown List

Jun 10, 2014

Attempting to use INDIRECT function to create dependent data validation list (Type). The tricky part is that the drop down options are dependent on the values of two (2) cell values (Brand and Country).

I have used the INDIRECT function before but dependent on ONE cell value only.

Have attached an example to better explain.

Example.jpg‎
Example.xls

View 4 Replies View Related

Dependent Dropdowns From A Sorted List

May 1, 2009

Dependent Dropdowns from a Sorted List:

i have one file here..

how did they created validation here.

is there any macros?

i tried..but i didnt able creat like this.


i got this file in this link.

http://www.contextures.com/xlDataVal13.html


i saw this problem in this post..

http://www.excelforum.com/excel-prog...hierarchy.html

View 6 Replies View Related

Third Dependent List With Last Two Lists Depending On First

Feb 21, 2012

How to do a third dependent list with the last two list depending on the first. The con textures site does not explain this.

View 1 Replies View Related

Data Validation - Dependent List

Aug 1, 2013

B2 should be drop-down list based on the value in B1.

Example: If Function code is 901, the drop down list should contains 3 values: Warehouse, Transport, QC only.

As we have ~100 function codes, formular with IF that I tried -> Data validation IF(B1="901",901,IF(B1="902",902 .... are not applicable.

I tried Excel Data Validation -- Dependent Lists but it didn't works.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved