Linked Validation Lists & Lookup Based On Choice
Aug 12, 2007
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
View 9 Replies
ADVERTISEMENT
Nov 5, 2006
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 Related
Jan 21, 2008
I 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.
View 3 Replies
View Related
Apr 13, 2014
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?
View 5 Replies
View Related
Jul 3, 2008
I have been going around in circles trying to work this one out... I have set up my "Work Packages" sheet ready to use with an excel timesheet. I have entered all ......
View 9 Replies
View Related
Nov 14, 2006
I have created a cascading data validation list, and I would like to have an adjacent cell auto populate according to the dropdown selection.
Data Eg:
Oranges 1100
Apples 1101
Pears 1102
Grapes 1103
If the user selects "Pears" from the dropdown list, I would like the adjacent cell to populate with the numerical code for "pears", in this case 1102. I am designing a new expense report form at the office, and I am at a standstill with this problem.
View 2 Replies
View Related
Sep 3, 2007
I'm looking for a method, vba macro or Excel code, to preform the following task:
In the C column the user chooses one of several options from a menu, let's call these options 1 - 10 (this part is already done). Depending on the choise, different things are supposed to happen:
For 1-9, the formulas for cell A - Q on the current row should be copied to the next row (but with +1 in row number in formulas of course).
For 10, 3 blank rows should be created (well they already are blank..), on the forth row down, A3 - Q3 should be copied exactly. On the fifth row, the same formulas thing that was created for choise 1-9 should be created.
Since the real xls-file is too big already and it contains some sensitive information, I can't attach it here, but I made a small and similar xls-file, that better explain what I want to do.
View 8 Replies
View Related
Mar 11, 2014
I've got a spreadsheet and I've used data validation to look at a list on another tab.
I need to be able to multiply values out based on what i choose on the drop down menu.
I've attached a sample spreadsheet to explain : tasks.xlsx‎
View 3 Replies
View Related
Sep 22, 2009
In all the cells in column F I have a validation list with several predefined choices, but at the bottom of this list I want an "Other..." option, which when chosen, enables the user to enter a new string in that cell if none of the predefined did not match.
View 7 Replies
View Related
Aug 10, 2008
I'm working on a simple worksheet, it is a tracking sheet for programs taught. What I'm trying to find is a code to insert a row based on a Data Validation List.
Exampl of data:
Column A is for " Name" then futher down Column K is "Program Taught" and Column L is " Date Taught".
Column K is where the Data Validation List is located with a in cell drop down.
What I would like to do is when a user selects a program from Column K a row is inserted with all of the formulas and formats from the previous row, and upon insert would like it to copy Column A into the new row.
This would be so I can keep a historical of programs and dates taught.
And to really spice things up I want to lock Columns K & L of the previous row after the new row is inserted.
View 4 Replies
View Related
Feb 6, 2008
I currently have a drop down menu in one of my worksheets, in which I have several different text values entered. What I would like to do is link each of those text values to a numerical value, which would be entered in to another cell. So if I select "Option A" from my drop down list, and Option A is equal to 200, I want "200" to show up in another cell. If I select "Option B" from my drop down list, and Option B is equal to 400, I want "400 to show up in that same other cell.
View 4 Replies
View Related
Aug 22, 2007
I have a way of getting a cell value from having a data validation linked to a row of cells containing lookup formulas.. which in turn are looing at a table.
I have attached a small example.
The problem is the spreadsheet is a large one and i was hoping I could shrink the size of this by removing the vlookup formulas by having some kind of index/match/lookup formula inserted directly in the data validation.
I got this to work using nested IF formulas but its not very practicable!
The attached example has data validation in column A that selects the task type. Choosing this then changes the values in the vlookup area.. which then becomes the validation list for cell in column B.
View 9 Replies
View Related
Oct 27, 2013
I am limiting choices in a certain cell to a list via data validation (using data validation, allow - list and pointing to the source). I want to limit the next set of choices in the next column (in this example in Column C) for the user based on the choice made in the adjoining cell. For example to keep it simple I will use the following: if the user selected "Fruit" in B1, then the options in C1 would only show "Oranges","Apples", "Pears". If however the user selected "Veg" it would only show "Cucumber", "Lettuce", "Spinach", "Radishes" in the drop down in column C. I can put a prefix in front of the column C data validation list if that is needed to narrow down e.g. FR-oranges, FR-apples etc.
A B C
1 Fruit Oranges
2 Veg Lettuce
3
Data Validation source for Column B (H2:H4)
Fruit
Veg
Nuts
Data Validation for Column C: (I2:I10)
Oranges
Apples
Pears
Cucumber
Lettuce
Spinach
Radishes
Almonds
Peanuts
View 3 Replies
View Related
Feb 15, 2014
I want to populate the value of a specific cell based on the choices made from two other dependent validation lists.
I am attaching a workbook that explains what I need to do.
View 9 Replies
View Related
Apr 24, 2008
I'm looking for a solution to a problem and from what I've read in the forums so far, this is the place to come. I first want to say that I have tried to solve the problem by using guidance from other similar threads and adapt those answers to my needs, but as my understanding of VBA is non-existent this was somewhat difficult. I don't want to post a repeat question and I hate wasting people's time if unnecessary, but I can't do this on my own.
That being said, here is the problem;
(I have attached a simplified copy of the worksheet I am working on for reference)
I would like to lock a particular cell in my spreadsheet based upon the selections made from two drop down validation lists (Cells “C4” & “D4”)
Essentially, if a correct choice is made in both, then the cell “E4” becomes unlocked for editing. If however, the correct selection is made in one list, but not the other, I need the cell “E4” to remain locked and a message to appear to prompt the user to correct their mistake.
Furthermore, both the validation lists have a whole range of selections that are very similar but have subtle differences;
E.g.
"Soft Eye by Talurit"
"Soft Eye by Splice"
Is it possible to write a VBA code that performs a similar function to the =IF(ISNUMBER(SEARCH("**",))) formula, so that if any choice that contains the word "Soft" is selected from either or both lists then the cell “E4” becomes unlocked, but if the selection does not contain that word then the cell remains locked?
If anyone could provide a code to make this happen, I would be very grateful! But please explain how to do this, as the word “Soft” won’t be the only parameter and I may need to alter the code so I need to understand the principle behind it; For example, on the spreadsheet, if in the ‘Inboard End’ list a ‘Soft Eye’ is selected and on the ‘Outboard End List’ a ‘Soft Eye’ is selected then cell E4 is unlocked for editing. If in the ‘Inboard End’ list a ‘Soft Eye’ is selected and on the ‘Outboard End List’ a ‘Fused and Tapered’ is selected then cell E4 is unlocked for editing, but if in the ‘Inboard End’ list a ‘Soft Eye’ is selected and on the ‘Outboard End List’ a ‘Standard Eye’ is selected then cell E4 remains locked and an error message appears to alert the user that his/her choices are incorrect. If however, "Soft" is not selected in either list then cell E4 remains locked (it has to be selected in at least one of the two lists to allow editing).
This is a work in progress (not all the formulas that will eventually be used have been entered yet, so please excuse the untidiness of it all) and the choices are likely to change based on input from my colleagues, so I need to understand how the VBA works in order to alter it accordingly, but I don’t want to put any more work into this (it’s still in the early stages) if it isn’t possible to achieve these needs.
View 9 Replies
View Related
Dec 1, 2006
I am trying to create dependent list based off of what was selected from the first dependent list.
Example ~> I select something from B3 that then drives what C3 says, but now how do I get D3 to be dependent on what was selected in C3?
I have attached my doc that I am working to get this on, can you take a look and point me in the right direction? The lists are on the other sheet,
View 5 Replies
View Related
Apr 24, 2014
I am trying to set up a spreadsheet with linked dynamic drop down lists, based on a table.Using the OFFSET function, I have managed to get this to work if the table is on the SAME sheet at the drop down lists. But I want to be able to put the drop down lists onto a different sheet, so that I can lock and hide the table.
I don't want to use the 'name based on the previous column' idea, as the table is large and will change weekly. the drop downs show all values, even duplicates, which I would like to get rid of.
View 2 Replies
View Related
Dec 4, 2008
I want to know how to link te two lists in my register so that when i select a particular route in one column the corresponding column automatically selects the transit time from the reference list.
View 2 Replies
View Related
Sep 26, 2007
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)
View 9 Replies
View Related
Apr 23, 2013
i am attaching a sheet here.
i have put data validation list in three columns
from data lsts i select unit type , then hinge type then code.... the problem is that as soon as i select code all the values (H W D ) in the next three cells should automatically change.
the values against each cabinet code are also provided in the same sheet.
Sr No.
Unit Type
Unit Type
Code
H
W
D
Qty
1
Base Unit
Double Hinged Door
B60
720
600
580
2
2
Base Unit
Single Hinged Door
B30R
720
300
580
4
View 2 Replies
View Related
Feb 16, 2012
In cell I2 I have a data validation list. If the selection is "2" then I want to copy and paste a value from another cell to another sheet.
I did a recorded macro but the code does not recognize the selection from the data validation celll, I2.
Is there some code I can add to get the macro to recognize what the user selected?
View 5 Replies
View Related
Mar 12, 2009
I have a Range on one sheet formatted like this and sorted by Sup then Rep:
Rep Sup
John Sally
Mark Sally
Joseph Sally
George Betty
James Betty
etc..
I have a Validation Drop Down list where a Supervisor is chosen. There's a specific range that I would like to automatically fill in with the reps assigned to the Sup. This is driving me crazy! I already know how to obtain that list as another Validation Drop Down, but not quite sure how to make those values appear in a range instead of a Validation List.
View 2 Replies
View Related
Dec 17, 2008
I need to get data copied from sheet1 to sheet2
depending on selection in a validation list.
The data is on the same row and all the same range.
So it would look something like this.
A B C
Fruit Orange Apple Pear
Veg Potato Carrot Onion
Animal Bear Cow Dog
I have the data in A in a dynamic range and validation list.
I need all the data copied from the specific rows ie
choose Animal from the validation list in Sheet2!A1 and
the entire row Bear Cow Dog get copied to Sheet2!B1, Sheet2!C1, Sheet2!D1.
The second problem I think is a lot more complex.
So the same scenario above but this time its a multi select on Sheet3.
I need to be able to select two (or more depending on Fruit and Animals to display in A1, A2 and then their valid options to appear in B, C and D
View 9 Replies
View Related
May 8, 2009
very complex spreadsheet for weight & balance calculations. It's to the point where everything works perfectly in Excel 2007, but it must be used primarily with Excel 2003. Discovered that a crashing problem had to do with condtional formatting, that's all been cleared and will soon be fixed, but there's one that I just can't quite figure out.
I'm using the worksheet change event to trigger the update of charts... In this case, it's looking at a particular cell that has data validation on a dropdown as the trigger. Works perfectly in 2007, and if I put a msgbox prompt in to be launched by a change of that cell, it launches. I can put the chart update code in a separate sub and launch it manually every time, but I cannot call it from the worksheet change.
I've included attachments showing what I believe are the relevant bits of code -
View 9 Replies
View Related
Sep 19, 2006
Is there a way to trigger a warning box (ie - must be divisible by 50) when a specific entry is selected from a pull down validation list? The triggering factor is a correct answer to the validation so I do not think the error alert will work in this instance.
View 2 Replies
View Related
Aug 9, 2007
getting a macro to dynamically assign a drop down box to a list of numbers?
Specifically, I have a list of numbers in column A, and I would like to dynamically (because the size of the column differs every time) add drop down boxes in column B so that a user may select different values in column A, simply by changing the drop down box value to "Selected"
This would this make it easier for collect all the "Selected" values and perform further analyses upon them.
View 5 Replies
View Related
Dec 11, 2012
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.
View 1 Replies
View Related
Mar 30, 2007
I need to find a solution that will prevent users from pasting values into cells that do not match values in a named range or validation list.
I think the best method would be to have it trigger with a SheetChange event. I would like it to check to see if the enteredpasted value(s) in a range of cells is equal to one of the following:
K
1
2
3
4
5
6
7
8
If it does not, I want the cell cleared and a message box shown.
I am sure this is simple, stock code for someone, but I am teaching myself vba and am fairly green. Does anyone have this handy in thier code library?
View 13 Replies
View Related
Jul 19, 2014
I will have users filling out cells (e.g. on Input sheet) and want to cascade the drop-down lists based on selections made. The basis for these selections is on the LISTS sheet.
So for Area, if they choose AREA1, then the Main Activity should only have 1,2,3,4,5 as available drop-down options and if they choose 1, then Secondary Task should only have A or B as options in that drop-down and finally Supervisor option should only be XX.
My question is, is there any quick way to create these cascading drop-down lists from the structure I have currently in place on the LISTS sheet. Or am I going to have to rearrange the data on a separate sheet? I will have up to 40 'AREAS' with dependent selections.
View 10 Replies
View Related
Oct 28, 2008
How to make a drop-down list appear when a cell is double-clicked as opposed to just clicking on the box to the right of the cell that appears when you click in the cell? I want to be able to use the ability to drag-copy the cell contents from the little square in the bottom-right of the cell, but cannot do this when there is a validation drop-down list!!
View 14 Replies
View Related
