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
ADVERTISEMENT
Nov 18, 2008
I have a list (external), which will be updated every month from an external source with new numbers and possibly a new size. This will be a count of services people use. On a 2nd sheet I need to allocate a cost to these services (pivotdata). This works fine if the list stays the same size, but it most probably won't so I need it to update automatically, not manually extending the list on the 2nd sheet. In brackets are the named ranges of the data in the spreadsheet.
View 5 Replies
View Related
Sep 11, 2013
I created comments to be shown when drop down list items are selected using the helper cell and workbook change solution from Leith Ross at the thread below.
[URL]....
This works perfectly to show the comments. What I'd now like to do is delete the comment if the content of the cell is deleted.
View 5 Replies
View Related
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
Apr 22, 2013
A lot of the Workbooks that I design for use by myself and colleagues require data to be copied in from external data sources. To avoid named ranges from failing, I always use the following method:
Calculate the length of the data set:
Code:
=COUNTA(INDIRECT("'Data Sheet'!"&"$A:$A"))
(There will be no gaps in the data, hence a count is fine.) This named range is called DSROWCOUNT.
Example named range for the data in column A:
Code:
=OFFSET(INDIRECT("'Data Sheet'!"&"$A$1"),1,0,DSROWCOUNT-1,1)
I use INDIRECT to ensure my named ranges do not fail if the data is deleted (accidentally or intentionally), as #REF! errors will occur.
The problem with this method is that it automatically makes the Workbooks volatile because of the use of OFFSET and INDIRECT, hence the Workbooks always needs to be in manual calculation mode to be usable.
View 7 Replies
View Related
May 25, 2013
Is there any way to print a list of Excel files without opening each of them manually?
View 1 Replies
View Related
Jun 20, 2008
I can't seem to find a way to make a data validation list automatically show the first item in the list rather than showing blank.
View 10 Replies
View Related
Jan 28, 2007
i'm writing a refrigeration selection user interface, working from values on an excel spreadsheet. how to get the programme to automatically select a value from a list or range once a user has selected corresponding value from a list within a combobox. for example if a user sets the temperature of their refrigerator to -5 celsius i need the programme to automatically select the corresponding value of enthalpy for the air at that temperature.
View 5 Replies
View Related
Jun 22, 2014
I have a cell in my spreadsheet that I use to control the size of a column. I formatted the cell as a number and added data validation to make sure the number is greater than zero. Then I realized that I need to also allow for the option of auto sizing the column as well. This would require a data validation list type with something like True and False for the choices.
What I want to be able to do is have it both ways. I want a drop down list in that cell with the options Auto Size or Custom. If Auto Size is selected the cell value gets set to that. If Custom is selected I then want to be able to type in a number.
ddl.png
[URL]
Is there a way to setup a cell such that the value is either an item from the drop down list or, entered directly (depending on the list selection)?
I know I can do it with dependent lists but that would require two cells, one for the auto size mode (true/false) and the other for manual width value.
View 1 Replies
View Related
May 19, 2014
I have pulled a SharePoint list into my workbook. The list object (table) is still linked to the SharePoint list, as I'd like to synchronize it later on. I have filtered it with an autofilter. I'd like to delete all of the visible rows. I have tried a billion things to no avail. I have been searching Google for hours now. None of the examples work.
View 5 Replies
View Related
Jan 9, 2014
I am working on an email marketing project and i have a small problem. I have two different email list. One (List A) is a large list of potential leads. The other (List B) is a list of leads we are not supposed to market to. I need to delete every lead on List A whose email address is also in List B, so that we do not send unwanted emails to our clients.
The best way i have to do this so far is to go through line by line, which is very impractical.
In case it matters here is out list format. Each list has 10,000 + leads. Each lead occupies a row. The row stretches across 13 columns and each column holds a different variable about the lead (names, state, email address).
View 2 Replies
View Related
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
Apr 24, 2008
Is there a way to quickly create a list of all dependent cells?
View 9 Replies
View Related
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
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
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
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
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
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
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
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
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
View Related
Feb 29, 2008
Two of them operate independently of each other, as in it doesn't matter whether a user selects yes or no, those are the two options. The validation list of the third question is dependent on the two others. Essentially if someone selects "No" to the first question, the only option for the third question is "N/A". If someone selects "Yes" to the first question and "Yes" to the second question, then they have the option of a yes/no drop down list. If someone selects "Yes" to the first question and "No" to the second, then the only option is "N/A." Here's my dilemma.
Since what I'm trying to create is going to be incorporated into a financial model that many people will use for to test a variety of scenarios, I'd like to make sure that the first option in the dependent drop down list (in question #3) displays automatically and changes if someone switches back and forth between different yes/no answers.
I've read how to get a dependent list to automatically populate the cell based on the selection in one cell (i.e. if question #1 is a "No", I can make the cell for question #3 populate with "N/A"). But since the validation list for question #3 is dependent on how someone responds to questions #1 AND #2, I'm not sure how to figure that out.
View 3 Replies
View Related
Apr 1, 2014
I need to create a 2-stage dependent drop down list.
Stage 1:
In cell G7 (on a tab called 'Data'), I have created a drop down list based on a range name called 'Region' .
Stage 2:
In cell G8, I need to create a drop down list which is dependent on the value chosen in G7.
Problem is, I will need to create a dynamic range name here which needs to vlookup or match the value from G7 in a dynamic list of data (the data is on different tab called 'RCA Data' and is spread over columns A:E with row A for headings - the data will go down an undetermined number of rows)
Every time the value in G7 matches a value in the 'RCA data' (the potential matches are stored in column
D) I need the corresponding cell value in column C to be added to the dynamic list. This list then needs to be available to choose from a drop down list in G8.
View 1 Replies
View Related
Jun 18, 2014
I am attempting to create a form in excel with dynamic data validation fields. What has me stumped is that I want to create multiple complex dependencies (for instance, if you select something from a drop-down list in the first field, that then gives you a specific range in the second field. Then based on what you placed in the second field it gives you a specific range in the third field.). What I can't seem to work out is the formula for making this work.
Is this impossible? Also sometimes the third field my be blank.
Here is an example of what I am trying to accomplish
Assets 2.PNG
View 1 Replies
View Related
Aug 4, 2014
I have a master list of names on sheet 1 column A. I want to create a list of names on sheet 2 column A of the people from Sheet 1 column A that have a specific value (Y) in column z sheet 1. I might also want to different numeric values for the Z column
View 7 Replies
View Related
Jun 20, 2014
I need to create a data validation drop-down that is dependant on what has been selected in another drop-down. However, all the solutions I've come across online so far suggest using the INDIRECT function, which is not appropriate in my case. This is because the data upon which the two drop-downs are based on is itself dynamic. My specific situation is this:
In one sheet, I have a list of university names in one column, and in the next column a list of faculties. The same university can appear multiple times in the sheet (once for each different faculty at the university). This information will be added to regularly, with new universities and new faculties added often.
In another sheet, I want a drop-down in one column that takes its validation from the list of universities. I then want a second drop-down that is only populated with a list of faculties at that university. The INDIRECT solution seems to only work if I can define a static range of cells from which to draw this data. How do I do it in my situation above?
View 3 Replies
View Related
Jul 31, 2014
I'm trying to create a form where you can select names from a list dependant on what depot has been selected
On Sheet 1 along the first row are names of depots, under each of these are a list of names (number of name varies)
On Sheet 2, C2 is a data validation list, with source being =Sheet1!A4:E4
Is it possible to make C3 show a list of the names that's dependant on what's selected in C2
View 2 Replies
View Related
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
Aug 11, 2009
I have worked on the excel sheet Test.xls(Attached) to create a simple uniform order form. Unfortunately, I am not able to get through the 3rd column. Let me explain. 3 Columns references below 3 steps
1. I selected the first dropdown item and selected the item
2. Corresponding value is shown based on the first choice.
3. This value or drop-down is not shown.
In my main "LISTS" sheet, I have items that include one of the item for example... Blue Socks & Shoes with characters "&" and spaces.
All this could be solved if I maintain the following idea.
1. Have the main list without spaces.
2. The corresponding heading is related to the items in the main list (without spaces).
3. Enter the Headings for the sublists and sub-sub lists without any spaces.
Great..I am getting lost with this..Can anyone help me.
You will get more clearer once you see the attachment.
View 10 Replies
View Related
