On Sheet1 I'm attempting to auto populate specific cells in columns B,C & D with information found in Data Validation lists (found on Sheet2) based on the "value" chosen from a list in column A. For instance,
If A2=Pig Then B2=Slop, C2=Pen, D2=Food
Is this best accomplished through VBA or a basic Function?
I've create a very large list using the procedures described in MatchingList sample workbook. For the most part everything works great however, there are about 4 list where when I name the range to capture the assoicated data, the name does not stick. I.E., I add a name with the title in $F$1 as "Address 1" and specify the range as $F$2:$F$8. When I select "Address 1" from the first cell and then go to the next cell to select the options, nothing is there. When I go to any of the cells below $F$1 I do not see the range name in the Name Box. Can someone explain why some of the names work and others don't.
View 2 Replies View RelatedI have a spreadsheet where I keep all records of all the rock climbing I have done.
I am after a way of choosing a climbing area either by data validation or combo box (already set up as a named range) and then the climb names available in another validation list shows only those from that particular climbing area.
From what I have seen from various web sites it seems the climbing area names need to be in a unique list (is this correct). My sheet looks like the following:
Column A | Column B (climbing area) | Column C (climb name)
Date | Mt Arapiles | Agent Orange
Date | Mt Arapiles | Auto Da Fe
Date | Morialta | Asgard
Date | Morialta | Al Sirrat
Date | Buckaringa Gorge | Agent Orange
The name in the climbing area column will be repeated often.
The climb name should be unique to each climbing area, but the climb name my be repeated in another climbing area.
The climbing areas are anamed range on a separate worksheet.
The details of the climbs has all the details of the climb i.e. the Climb Name; Climbing Area (VLookup); Grade ; Distance ; Single or Multi Pitch ; Lengths of each Pitch.
I already have a working example of the spreadsheet (220KB) but it currently works backwards i.e. I use validation to find the climb name and then this uses the INDEX function to return the climbing area.
I would like to be able to choose the climbing area and then the next available choices would be limited to that climbing area.
I'm trying to continue on this post: Populate Cells After Choice From Drop-Down
I'm trying to expand on CNL's idea. What I need to do is create a spreadsheet that autopopulates two blocks based on the dropdown box that preceedes it. I've got it to partially work, but can't figure out the next step. The differnce between my sheet and CNL's sheet is my drop-down is an indirect data validation.
The easiest would be to have my data in one long list, but my goal is to make it easier on the user to find the specific procedure (I'm trying to build a budgeting spreadsheet for a medical office). So in box A15 there is a list of the different service area, which gives a list of choices in C15 based on the choice selected in A15. My goal is to have G15 and G16 list the cost and charge respectively.
M1 - O3 is the data for Radiology
Q1 - S4 is the data for Labs
U1 - W4 is the data for Office
I am using excel 2007
I am attempting to put a drop down list using "Data Validation". I can get it working when my list is on the same sheet (sheet 1) and the column of cells I want the drop down list to show up in....(you know..when the drop down list shows up in each individual cell)......BUT...when I put the list on another sheet (sheet 2) and try to do the "Data Validation" back on sheet 1, excel won't let me go highlight the list on sheet 2.
I even tried writing sheet2 and the range and that still doesnt work.
I would like to know if it's possible to populate a data validation list based on what is selected from 4 validation lists?
for example:
On sheet1:
If 'Group1' is selected from data validation list1 then data validation list5 will show a list of all items from Group1. If 'Group2' is selected from data validation list2, then data validation list5 will display all the items in 'Group2'...
(I do not want to use a combo box for this)
I’m a fairly basic excel user, I think I have a fair idea of what I’m trying to achieve.
I’ve tried to take the time to explain my query so if anyone is happy to help, I’ll definitely take the time to give a decent response.
I’ve broken it down abit.
Background
I’ve created a form for which users select a series of drop down boxes, as each drop down box has a value selected, the options available for the proceeding drop downs are filtered and the options then become limited.
In order, the drop down boxes are;
1. Select a Utility – the data validation source list is “utility”GasElectricity2. Select a Distributor List of 3 Gas Distributors the data validation source list is “ElectDist”List of 5 Electricity Distributors the data validation source list is “GasDist”If user selects Gas as a Utility as per dropdown list #1, then the option shown in “GasDist” validation source list will appear.
If user selects Electricity as a Utility as per dropdown list #1, then the option shown in “ElectDist” Validation source list will appear.
3. Select a Service the data validation source would be either “Eservice” or “GService”Special Read Investigation4. Select a Sub Type the data validation source lists are either “EReadSub, GReadSub, EInvestSub, GInvestSub”
In column G, users select one of three values. I want a list of additional options (my named ranges) to appear in column H based on the selection of column G. I can get this to work when just referring to one of the named ranges, but not all three. I also do not want any of the data to appear if there is no selection in column G.
My not quite right code:IF($G$5="Code of Conduct",COCList,IF($G$5="Integrity",INTList,IF($G$5="Behavior",BEHList,"")))
Excel spreadsheet will contain a form that will consist of drop down boxes (data validation). Each drop down box will define the data that can be selected in the next drop down box.
The way I initially went about it, is creating nested IF statements. However, we all know there is a limit of 7 nested IF statements. I have 10! So below formula does not work:
=if(B5=1,F5:I5, if(B5=2, F6:I6, if(B5=3, F7:H7, if(B5=4, F8:J8, if(B5=5, F9:H9, if(B5=6,F10:G10, if(B5=7,F11:H11, if(B5=8,F12:H12, if(B5=9, F13:I13, if(B5=10, F14:K14))))))))))
I tried other workarounds such as CONCATENATE, or & signs. No luck. VLOOKUP does not work also, because there are multiple columns in col_index_num. Anything else I try gives me this message:
“The List Source must be a delimited list, or a reference to a single row or column”
What should really happen is this:
User selects value in first list box. Second list box shows values associated wih the value from the first drop down only.
I have a very large spreadsheet that I work with on a daily basis. The main portion of the spreadsheet is broken down into weekly sections (for example, columns GF thru GM contain data sections for one week, which then repeats with the next set of columns, etc.). Below these weekly sections are detail and summary sections for all the data entered above. In the main weekly portion, each week contains a size drop-down list which calls to size information in the detail portion below. Recently, the data validation lists have started disappearing. By disappearing, I mean that the data validation for the cell no longer exists on a large portion of the list cells. I have tried recreating all of the data validations, saving the file, and re-opening it, and when I do, they've disappeared again. I have no idea what to do at this point. The functionality of the spreadsheet is crippled without the sizing drop-down lists (for various lookup reasons).
View 14 Replies View RelatedSee the attached example. I have created a dependent data validation. The list available in B2 is dependent on the item chosen in Cell A2.
What I would like to know is how do I expand this validation down columns A and B. I don't want to have to add the validation in each cell individually!
I cannot seem to add more dependents using this code. I think it is in the "If / then / else" structure that it is limited to only those three "groups". But I need to be able to add at least one more dependent list to the code.
View 4 Replies View RelatedI am trying to make two dependant lists in Excel. Using data validation lists, is it possible to, for example, select a country from one list, then select a city in that country from another list. For example, if I select France from one list, I only want the list of cities to include French cities, rather than the entire list of cities in the list.
View 2 Replies View RelatedI have an Excel spreadsheet with two lists of data, the first is a list of groups and the second is a list of sub-groups, each group containing a number of sub-groups, each sub-group being unique and belonging to only one group.
Elsewhere on the sheet, I have used Data Validation (using 'list') in 2 columns to provide a drop-down selection in cells which the user must complete. The first column refers to the first list (groups), the second column refers to the second list (sub-groups).
However I would like to make the values which appear in the second drop-down list dependent on the value which has been selected in the first. That is: make the user choose a group in the first column and then make the choice of available sub-groups in column 2 restricted to those sub-groups which belong to the selected group.
I have a question regarding linking the content for data validation lists stored in a different workbook.
The scenario is as follows: I will have say 50 users using a template which contains various dependent drop-down lists. These lists work by using the INDIRECT function which calls the correct drop-down based on the previous selection. The drop-down entries are currently stored in named ranges on a hidden worksheet in each user's file.
However, if I need to make a change to the content of these lists, I will have to open all 50 files individually and change each one. So I'd like to have a master file to which all the templates would be linked which would contain the entries for each drop-down. So when I update a list in the Master file, it would automatically update the drop-down lists in each user's template with any additions deletions.
My question is, how do I create the links between the user templates and the master and have it so that the list ranges will be dynamic. So if a drop-down list contains say 10 entries and I add 2 more in the master, that those 12 now appear in each user's template?
I have a question regarding the data validation lists. What i have is 4 different lists
Department
Assays
Instrument
Manufacturer
What I want to do is...once a user chooses a department, he gets a list of assays done in the chosen dept. and also a list of instrument used by the dept and a list of manufacturers
I used the examples shown here
http://www.contextures.com/xlDataVal13.html
http://www.contextures.com/xlDataVal02.html
and was able to succeed in creating dependent lists to some extent...I am attaching whatever I have able to accomplish so far, which is able to pull up the assay list based on the dept. ...but am confused as to how to get the Instrument and Manufacturer list at the same time .....please choose 'Chem 2" from the dept list to see what I have managed to do so far
I am creating a spreadsheet i want it to be that i make a selection from a drop down and this will then narrow the options available in the next drop down. Say box one you select 'half day' and then the next drop down menu will automatically loose all the data for a 'full day' and only show half day information such as costs for food and room bookings ect. i have made the drop downs but im not sure what to do next.
View 3 Replies View RelatedSheet1 is my database and looks like the following:
HTML ABCDEFGHIJK
1RefNoSetSubsetStatus
2101lambsheepINP
3102catpetCOM
4103chickenbirdINP
5104milkcowINP
6105turkeybirdINP
7106honeybeeCAN
8107dogpetINP
9108boybabyINP
Sheet2 is for the user input, in which Columns H and I would have a data validation drop-down list and looks like the following: ...
I have three lists and I want to summarize them in one table. I can do this by formula or using Data validation by position. Who is the best way? There is a difference between them?
View 3 Replies View RelatedOn sheet 1 I enter data such as:
Visit Date | Customer Name | Customer Number | Job Number | Product
On Sheet 2 i have a list of our 130 products which would be similar to '1130Tots Standard 24" ' I have made these as a list so we can choose from the list using data validation.
Now, out of the 130 products we have about 4 main different Catagories - Tots Direct, Tots RTS, Direct, RTS. - I could put what category they are in in a column next to the list on Sheet 2.
What i would like to happen is if e.g. a product is chosen from the drop down list, it could look in the cell in the column next to it and see what Catagory it is in and format the entire row on Sheet 1.
E.g. Direct changes colour to grey, Tots RTS to Pink, Tots Direct to Grey/Pink stripes, RTS stays standard no colour.
A bit of a development on a question i asked last week to cut out another stage. Currently people have to enter the product, then choose in the next column whether is is Direct, RTS, Tots Direct, Tots RTS then conditional formating will colour the rows. - Want to know if it is possible to do this all in 1 go by selecting the product from the list.
I have a need to use data validation for user selection of dependent lists, but dependent upon what the user picks from a previous list I need some selections to disappear or become hiden somhow.
For example, let's say I want to first pick from a list of Countries, then States, then Cities, but some of the Countries do not have States, just Cities and some have both. When a Country does not have any States I need the States data validation drop down box to disappear and the cell to become blank so that the user knows there is no selection needed.
I know how to make the dependent data validation lists, but I do not know how to make one of these drop down lists disappear based upon a previous selection in dependent lists.
I have on a sheet:
4 data validation lists that contain the exact same values.
would like to have:
One validation list box that is dependant on all 4 vd lists.
I posted a thread on dv and came to the conclusion that it is not possible to use INDIRECT() as it only allows for one cell reference and not four.
Is there another way to do this?
Glove Man, I tried your suggestion but couldn't get it work.
I'm trying to create a cell with multiple dropdown lists in E4 depending on a value from another drop down list in B4. Both E4 dropdown lists are named cell ranges RV_MECHANISM and VALVE_OPERATING_MECHANISM_TYPE. I know how to do this in Data Validation using an IF formula, however, that's not quite "bullet proof" or "idiot proof" enough.
I'm attempting to create a macro to load only the appropriate dropdown list in E4 as called for by B4.
I recorded the Data Validation dropdown lists using the macro recorder, then searched through all appropriate posts I could find at MrExcel and attempted to write the appropriate macro code as follows;
Sub RVorMECHTYPE()
With Selection.Validation
.Delete
.Add Type:=xlValidateList
If Range("$B$4").Value = "RV" Then
ValList.RV_MECHANISM
Else
ValList.VALVE_OPERATING_MECHANISM_TYPE
End With
Range("E4").Select
End If
End Sub
Compile error: End With without With
That's as close as I can get ... can one of you kind souls direct my macro coding paths and offer up the correct code for accomplishing this task.
I have a data validation list in B31 "material type" that has 4 options (Adhesives, Metals, Backer Board, & Grout). Based on that selection F31 needs to display a list of "descriptions" that match that "material type". The list is in book "database.xls!sheet3". Material type list is Column A, Description is in Column E. I know that you can't link to an unopened workbook in data validation, & I've been trying to code it, but again the lack of expierence & knowledge has frustrated me again :P This code to get the info for the file to open
Private Function GetValue(path, file, sheet, ref)
Dim arg As String
If Right(path, 1) <> "" Then path = path & ""
If Dir(path & file) = "" Then
GetValue = "File Not Found"
Exit Function
End If
arg = "'" & path & "[" & file & "]" & sheet & "'!" & _
Range(ref).Range("A1").Address(, , xlR1C1)
GetValue = ExecuteExcel4Macro(arg)
End Function....................
I've looked at the data validation tips and understand how to make a second dropdown dependent on the 1st, but how about a 3rd dependent on the prior 2 selections and so on.
I would like to have dropdowns based on a dataset of 4 columns (Location, Department, WorkGroup, Employee) that frequently gets updated. I would like to have the user select a location then a department then workgroup and employee. I would then fill in a worksheet with other pertinent information. I can get the 1st two dropdowns to work, but haven't found the key to being able to continue to drill down based on the 1st two options.
I have a cell that I want to use data validation on so I have a drop down list. Problem is the location of this list will be in another workbook.. Is this possible to go from one workbook to another using data validation?
Also, depending on the information that is selected from the drop down list I want a cell to the left to pick the corresponding data from the list in the other workbook. These forms will always be in the same folder.. Not sure if that information is necessary but just in case you need to know.
I've got a table that has to be manipulated by multiple users. Within the table I have several column ranges with validation lists (in drop down boxes) in order to limit responses. However, I'm finding that most of the users are pasting data in from other sources that may not have been in the validation list, or may be in a different format, etc., and it essentially undermines the reason for using the validation.
I've tried protecting the sheets using various options and tried looking around online for a solution, but I'm not finding how to get around this.
My Table has a data validation list in one row of a table. And different formulas in different rows.
The Table is expanded only into columns. Which means, there will be no new rows...only columns will be added.
If we enter text into the header cell in a new column, the whole new column gets formatted, but the list and formulas do not auto fill into new columns.
Of course an easy way is to copy and paste a column...but is there a way to auto fill lists and formulas into columns of the table? Similar to how the rows get autofilled?
I have an Excel file with two different visible sheets (dashboards), each with different types of charts, which are pulling from the same data tables on a hidden sheet. I have a data validation drop down list on one of the dashboard sheets, which lists 7 items. Once one of the 7 item is selected, both dashboards update, which is what I want. However, I'd like to be able to have duplicated, related validation drop down lists. So if someone is looking at the first dashboard sheet and they select a new item, when they go to the second dashboard and see that same item, they could on that second dashboard select a new item without having to go back to the first dashboard.
I'd provide an example if I could, but cannot. I've tried searching on the forum for something similar, but most often the topic of dependent data validation is on two different types of drop downs with the second being dependent on the first, whereas I'm looking for two drop downs that are interconnected and can update in sync.
I am having trouble delete a drop down list inspite of selecting clear all from the data validation tab in excel 2007. When I hit Alt + Down Arrow, I still see picklist options. I didnt set up the spreadsheet, hence I am not sure how to begin troubleshooting.
View 1 Replies View Related