Show List Dependent On A Cell Content?

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


ADVERTISEMENT

Dependent Dropdown List - 3 Levels - Remove Common Entries From Selection - Show Relevant

Dec 4, 2013

I have scoured the net for the following and have not been able to find a solution. I have found variations but not something specific for the use needed here.

I have attached a file with sample data which has the following format.

Column 1 = vehicle model (in this example golf, jetta, but there will be over 100 choices)

Column 2 = vehicle package option for specified vehicle model (automatic transmission, manual transmission, automatic transmission with air conditioning, etc...)

Column 3 = vehicle colour available for vehicle package option

Please note that Column 2 values for 'golf' are different than values available for 'jetta' (in the sample data I have blocked out common values with the same colour for quick and easy identification)

Also please note that Column 3 values of colour options vary for each 'vehicle package option'.

The behaviour that would be ideal is to have 3 drop down menus. The first drop down menu will allow selection from column 1 and will show each model only one time and repeated values will not be shown. The second drop down menu will only show the options available based on the selection in the first drop down menu. Likewise, the third drop down menu will show the colour options available based on the selection in the second drop down menu.

Since my data will involve hundreds of unique values in column 1 with plenty more added over time, it seems that the format of the data and the way it is laid out in the attached sample is the easiest way to organize it. It is perhaps also the easiest way to include new data without a lot of reprogramming.

Sample Data - Dependent Drop Down Cells Question.xlsx

View 8 Replies View Related

Show Content Of Particular Cell

Feb 9, 2009

I have 10000 data in my column A. i can genterate random number from 1 to 10000 through function "randbetween".

Now i want to display the particular data which column a and row as per that random number. in short i want to display data of "column A row (generated random number) for lucky draw selection.

View 2 Replies View Related

Automatically Expand Row Depth To Show Cell Content

Jan 3, 2006

I have a spreadsheet with a master front page, which basically copies the contents of other cells in other sheets for it's own content. ie:-

the formula in cell E16 on the master sheet is ='Sheet 7'!$B$16 (Please note that cell B16 is actually a merged range of B16 to H16)

Everything works fine until such time as the orginal cell contains an amount of text which is deeper than the cell on the master page eg cell B16 on sheet 7 is 330 pixels deep while the cell E16 in the master sheet is only 16 pixels deep.

What I would like to happen is for row 16 in the master sheet to expand in depth by whatever is requried to automatically show the full content of B16 on sheet 7.

View 9 Replies View Related

2003 Pivot Table: Show The Content Of The Cell (non Calculation)

Apr 16, 2009

i have a database of the kind:
Model Version Color Store
1 a zz HH
2 b xx QQ
3 c yy OO
4 d ww PP

I need to obtain in the y axis the model, and in the x axis the store.
The data per each model and store should be: the version and color.

I'm using Pivot table to do that, but i'm only able to obtain the "count" (or sum or other formula) but I'm not able to obtain the content of the cell.
Example:

Store HH PP
Model
1 Version a d
Color zz ww

2 Version b c
Color xx yy

View 9 Replies View Related

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

Create A Name List From One Sheet Dependent On Value Of Another Cell

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

Excel 2010 :: Calculating Value And Posting Total To 1 Of 4 Fields Dependent On Content Of Another

Jan 9, 2014

Excel ( 2010 ). I am creating a Sales Leads spreadsheet. Within the spreadsheet I have a the following relevant fields.

proposed sales value field ( F2 ), Estimated Close - which will be Q1,Q2,Q3 or Q4 ( G2 ), % Probability ( H2 ) & Q1 ( I2 ), Q2 ( J2 ), Q3 ( K2 ) & Q4 ( L2 ).

F2 = 150
G2 = Q2
H2 = 10%

I want to calculate the actual value of sales based on %prob and put the total into the correct field ( I,J,K or L ) based on what field G2 says. So in this case 15 into field J2. I have attached an example.

View 4 Replies View Related

Macro To Strikethrough Row Dependent Of Cell Value In Dropdown List

Jul 16, 2008

Is it possible to have a worksheet code that would underline a row if a certain value is selected in a dropdown list?

I have a worksheet with data populated across colums A to K, in Column I there is a dropdown list. What I wanted was to be able to understrike the Columns A to K of a particular row is the value in the dropdown list in Column I ="Cancelled". I cannot use Conditional Formatting as I have already used the maximum 3 formats for other features.

View 9 Replies View Related

Create Dropdown List With A Range From Dependent Cell?

Oct 30, 2013

I have the following table with weeks to display for the holidays and my column H has a drop down list of the first column in the table below with the Named Range, "SNCODE.Season_Codes".

The other two Named Ranges are, "SNCODE.Season_Codes.Start" and "SNCODE.Season_Codes.End"

I need my column K to display a list of weeks from the first week to the last week. For example, the cell in column K for Valentine's Day should have 1, 2, 3, 4.

Basic 1 53
Promotional 1 53
Seasonal Spring 1 13
Seasonal Summer 10 26
Seasonal Fall 23 39
Seasonal Winter 36 53
Valentine's Day 1 4
Halloween 31 40
Christmas 36 48

View 6 Replies View Related

Drop Down List Based On Cell Content

Jul 22, 2007

I'm trying to find a macro that will allow me to enable several drop down lists in the same cell based on the content of another cell. In other words:

If cell A1 is the data entry cell, and the user enters "cat"
Then cell B1 will display a drop down list of previously grouped options: "mouse, bug, bird"

If in cell A1 the user enters "dog"
Then cell B1 will display a different list of previously grouped options: "mole, rabbit, kibble"

View 2 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

IF Content Has A Value Show

Feb 10, 2010

I have a table in Excel 2007, I need to show the content of a column if it has data and if not leave empty, I think I need an if statement.

Column A may have content in a row but could have empty cells
Column B would be the same, but different rows, Column C to have the answer.

Like this

ColumnA ColumnB ColumnC ("Result")
Awe4 Awe4
Bye Bye
Empty Row
b78 b78
b65 b65

View 15 Replies View Related

IF Formula - Column To Show Text Value Dependent On Combination Of Three Columns

May 12, 2014

I have three columns that each have a drop down of text options; column A has three options, column B has three options and column C has two options. I want to have a fourth column that will show a text value dependent on the combination of the three columns.

It ends up being 18 variations so my IF formula just won't compute.

This is basically the format I have and works for a few values then just chucks up an error when I extend it to all 18 variations:

IF(AND(A1="text",B1="text2",C1="text3"),"show this text",
IF(AND(B1="other",B1="other2",C1="other3"),"then show this", ) etc etc

Any way I can get this to work for all 18 possible outcomes??

View 6 Replies View Related

Series Of Dependent Comboboxes In Userform That Show Unique Values Of Dynamic Tables

Mar 23, 2014

This example workbook contains a datatable, which is inputted via a userform. The datatable has 4 columns: Date, Invoice no., Loads, Tonnage. This table is dynamic, as a new row gets entered each time data is entered in the userform. A different userform (the one in the example) has comboboxes which refer to the data in the table. This userform asks the user for the Date, Invoice no., Loads and Tonnage. I want to use comboboxes so that they will advise the input based on the users previous input.

The first combobox asks the user for a data, and should contain a list of all the unique dates that are stored in the table. When the first combobox is inputted, the list for the second combobox will change. The list of the second combobox should be a list of unique invoice numbers, based on the date that has already been entered. The 3rd and the 4th combobox should also show a list of unique values, based on the previously entered date and invoice no.

Example: The first combobox should advise the dates: 4-Nov-14 and 15-Nov-14. User chooses 4-Nov --> second combobox should advise unique invoice numbers based on chosen date: 1252 and 1311. User chooses 1152 --> 3rd combobox should advise unique values based on previous values: 3, 8 and 7. 4th combobox: 57, 23 and 47.

View 3 Replies View Related

Read Cell Content & ADD Content + Font Properties *SOLVE

Sep 12, 2007

I use this to read cell content, add some text/characters (ie. [ and ]) and change the properties of the complete cell

Sub COMMENT()
Worksheets("DVD Lijssie").Activate
If ActiveCell.Value 0 Then ' Change all in to ... ... ...
ActiveCell.FormulaR1C1 = ActiveCell.Value & " " & "]" & " " & "["
With ActiveCell.Font
.Name = "Arial Narrow"
.Size = 8
.ColorIndex = 16
End With
End If
End Sub
HOW can I change this vba-code so it leave's the content of the cell like it is and add some content with the use of let's say TexBox1 and ONLY use different font properties for the newely added content?

View 9 Replies View Related

Highlight A Textbox' Content When Userform1.show

Apr 27, 2006

Sub Thanks()
With UserForm1
.TextBox1 = "1111111111111"
.TextBox2= "BBBBBBBBBBBB"
.TextBox3= "Tres"
.TextBox4 = "44444444444444"
.TextBox5 = "Five"
.TextBox5.SetFocus '''''''''''''''''''''''''''''''''''''''''''
.TextBox6 = "666666666"
.Show
End With
End Sub

is there a way to highlight the content of the TextBox5 when Showing the Userform?

View 3 Replies View Related

Change Content Of One Cell Based On Content Of Another

Apr 18, 2014

Formula/macro/etc that would enable me to have content of a cell changed based on the content of another cell in the same row.

Example: cell in column D says "PSA" - so I would need the cell in column H for that same row to read "Radio"

I would need an entire sheet scanned to review for these occurrences and make the appropriate changes. I also would need the formula to be inclusive enough to scan for variations in column D cell content (PSA 1, PSA 2, etc).

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

Drop Down List :: (the Arrow In The Square) Does Not Show Up Unless I Click On The Cell

Apr 17, 2007

I have created a drop down list but notice that the button (the arrow in the square) does not show up unless I click on the cell. Is there a way to show the button at all times so the user knows a drop down list is available?

View 9 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

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

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 View Related







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