Child & Parent Data Validation List

Dec 10, 2007

I have data for , say - Cars, Company and Average.

I solve this upto 2 level i.e cars and companies.

But i want a third level also.

When i select Car from drop down list, using data - validation - list , in cells(1,1) , i should get all companies in Cells(1,2) and on selecting companies , i should get Average in cells(1,3).

If i do define name ,and then data validation list , and then use Indirect() function, i can solve it upto 2 level.

Eg. I can get company for car .. but how to get Average Also.

View 8 Replies


ADVERTISEMENT

Excel 2010 :: Comparing / Merging Child Spreadsheet (purified Data) Into A Parent One Raw Data)

May 17, 2014

I have a parent spreadsheet with raw data(with errors) and a child spreadsheet without errors. I want to merge the child into parent. (:{). I am thinking of comparing multiple columns from each sheet to ensure maximum accuracy. And when those columns match up we paste the corrected column data from child sheet to parent one. I am using windows 7 and Excel 2010.

View 1 Replies View Related

How To Get Parent Row Into Child Rows

Feb 22, 2013

[Code]......

(and about 5000 more sets with garbage page headers in between the sets of parent rows)

Basically the data spit out by machine is separated by a blank row.

Let's say I need all the data with the child row "tn" , so I would autofilter /PivotTable it for that entry, but that leaves me with a blank column A. It'd be no issue if I had the column A heading in the child rows (and the number of spaces in between is random like 2 sometimes 15+ so Vlookup/Index/Match aren't too useful). It is possible to have a function that repeats the first row until it hits a space?

Like so:

[Code] ......

Then I could autofilter/pivottable to get:

[Code] .....

View 1 Replies View Related

Indented Parent Child Relationship

Jul 19, 2014

I have the following table and i need to be able to present it an indented list of parent child relationship. The hierarchy can go 1000+ levels deep. How do i achieve this using VBA?

OName
OPackID
PPackID
PName
ParentID

Name1
556
556
Box1
389

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

If there is a better way of presenting it, it isn't limited to an indented list.

View 5 Replies View Related

Create Parent Child Relationship From Hierarchy

Feb 7, 2008

I have an excel file (see sample attached) with a hierarchy with parents and children spread across different columns and rows. However, I am trying to condense it into one column with parents and one column with children. The only way I've been able to do this is by copying and pasting parents onto children rows, which is both a manual and time-consuming process (when you have a lot of rows). I feel like there must be a way to use VBA or a formula or something to get it to work. I have attached a sample, so you can see what I am trying to do (current data and desired data).

View 6 Replies View Related

Display A Tree-like Structure From Parent-child Nodes

Oct 1, 2008

I encounter the following problem which has been bugging me for weeks.

I am doing up an engineering spreadsheet which calculate the electrical cable size from node A to node B.

Col A and B goes like this:

From|To
Transformer|MSB_A
MSB_A|SSB_A1
Transformer|MSB_B
MSB_B|SSB_B1
SSB_A1|DB_A1
SSB_B1|DB_B1

So Transformer is the parent of MSB_A and MSB_A is in turn the parent of SSB_A1 and so on.

What I would like to achieve is to put these data into the following configuration:

Transformer----->MSB_A
............................|-> SSB_A1
.....................................|----->DB_A1

Transformer----->MSB_B
...........................|-> SSB_B1
....................................|-----.>DB_B1

NB: Please ignore the "dots" above.

This will give the user an overview of the parents and node relationship.

I can't seem to find a solution how to achieve this.

View 9 Replies View Related

Align Numbers From 2 Columns In Parent Child Manner

Apr 5, 2008

I have searched numerous times for the answer I need, but no joy yet.

I have a spread sheet that has one column (call it 1) of data with a single entry each of 100, 200, 300, 400, 500, and so on.

I have a second column (call it 2) that has multiple entries of 100, 200, 300, 400, 500, and so on, and I want to sort so that the rows wind up with the numbers in column 1 all sorted in an accending order, but I want all the same numbers in column 2 sorted to "list" next to the same number before the next number sorts. See below for a small example of what I hope to see once it sorts.
100
100
100
100
200
200
200
200
300
300
300
300

Auto Merged Post Until 24 Hrs Passes;Unfortunately the example didn't post correctly..perhaps this will work but note that the x's are in column 1 and don't exist.

100
xxx100
xxx100
xxx100
200
xxx200
xxx200
xxx200
300
xxx300
xxx300
xxx300

View 5 Replies View Related

IE6 Automation In VBA - Open A (child) Page On Clicking A Button In Main (parent)

Jan 29, 2010

My query is regarding internet explorer 6 automation via code in VBA (Excel 2003). I'm trying to open a page on clicking a button in main page.

I'm able to open both parent and child pages via VBA, but now want to automate events (like radio btn click etc) in child page.

below is my initial code -

'========
Sub ListLinks()
Dim IeApp As InternetExplorer
Dim sURL As String
Dim IeDoc As Object
Dim i As Long

'Create new instance of IE
Set IeApp = New InternetExplorer

View 9 Replies View Related

Data Validation Used To Create Separate Data Validation List

Feb 15, 2014

I have 2 columns First Name & Surname. What I want to do is create a data validation list on the surname which results in the 2nd data validation list only showing the first names which link to one of the surnames.

i.e. If I selected Smith in the 1st validation list then I would only like to see 'Paul' as an option in the 2nd list

First Name
Surname

Paul
Smith

Paul
Jones

Tony
Phillips

View 1 Replies View Related

Choose Parent And Return List Of Bottom Level Children?

Apr 16, 2014

I have a large flat hierarchy table. I am looking for a method to select a parent and return a list of my lowest level children. I am somewhat familiar with VBA but I don't know where to begin coding this.

This has been a great learning source over the years, but this is one of the few instances I cannot find my problem so I am now posting. Example below:

Child
Parent

4
5

3
4

2
3

1
5

40
50

30
40

20
30

10
50

22
100

If I select 5, I want values 2 & 1 returned.
If I select 50, I want values 20 & 10 returned.
If I select 100, the value 100 should return.
If I select 40, the value 20 should return.

View 6 Replies View Related

Data Validation List Automatically Show The First Item In The List Rather Than Showing Blank

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

Named List: Data Validation To Restrict The User To Only Selecting Values In A List

Jun 15, 2007

I'm trying to use data validation to restrict the user to only selecting values in a list which I create. Right now, the list is a named range. I'd like to get rid of the range and just use a named list. I create a name using the following as my list.

Insert > Name > Create
Name: Fruit

Refers to:
banana,apple,orange

When I try to use the name Fruit in my data validation, I get the message "The List Source must be a delimited list, or a reference to single row or column." I thought my name "fruit" was a delimited list.

View 4 Replies View Related

How To Get Unique List From A Column To Data Validation Dropdown List

Oct 28, 2011

I'm looking for a way to get a unique list from a column to a data validation drop down list. Any fancy formula or vba script to create a UDF which. Does this?

View 5 Replies View Related

Excel 2007 :: Data Validation List Depend On Another DV List

Apr 29, 2012

I have a List of Different Fruits in Cells A1 to A5

Apple
Banana
Orange
Strawberry
Cherry

And I use data validation list in 5 different cells from Cells C1 to C5 then in every cell the list will show all the fruits,

But I want that if I select Any Fruit in cell C1 that should not be included in the remaining 4 cells, and the fruits selected in Cells C1 and Cell C2 should not be included in the remaining 3 cells and so on....

I Used the formula

=IF(C1=A1,OFFSET(A2,,,COUNTA($A$2:$A$5),1),0)

But this works fine if I select Apple in the Cell C1, then the List of C2 Shows all Fruits other than Apple, But if in Cell C1 I select any fruit other than Apple it does not work... (Using Excel2007 & Win XP)

View 4 Replies View Related

Setting Widths List Box In Data Validation List

May 12, 2004

dear....can i set width in list box data validation (width in cell i set short).thanks

View 9 Replies View Related

Force Choice From List: Data Validation List

Oct 18, 2006

is there a way to force so that a cell value can be only of what a validation droplist offers, hence users can not enter their own values and have to use the droplist for cell content selection.

View 2 Replies View Related

Data Validation List, Disallow If Not In List

Jul 16, 2009

I am using Data Validation on some fields to create a drop down list from a named range! These fields however allow you to enter values that are not in the list.

Is there a way to make the cell have to be an entry from the data validation list?

View 6 Replies View Related

Copy Data From Child Workbooks And Paste In Master Workbook

May 25, 2009

I had to copy data from child woekbooks (*.xls) and paste it to the master workbook with same page to page every time when a macro is executed i had done the copy and paste part

But I'm Facing the problem in which i had to deal with

Validations as on both master and child sheet validation (column based combo box is activated )
one is worktype
2ns is time type

i jst had to copy data to the master macro works perfectly fine but the problem is that a msg box appears which signifies that i had to change the name (version ) for both types when i click yes 2 times it pastes the data

I'm attaching my macro as well as pic of that msg box with this attachment

View 6 Replies View Related

Data Validation - How To Create Dropdown List That Excludes Duplicate Data

Sep 26, 2013

I'd like to create a drop down list in data validation from a column of data that contains numerous duplicates.

For example, let's say column A contained hundreds of transactions with either North, South, East and West, how could I create a drop down list in another cell that only had four selection options?

View 5 Replies View Related

List Data Validation

Mar 18, 2009

The idea is to create a liquor order sheet which takes items and prices from a liquor inventory workbook, which is part of the same file. i'm sure that you can do it all fancy with a macro, but unfortunately i'm not super experienced with excel. so i helped myself with what i know a bit -> drop down lists. I created an extra sheet which has 4 ranges on there... the liquor type (liquor, beer, wine), and the 3 sub classes for each type (red/white/blush/sparkling for wine, draft/import for beer and so on and so forth). On the inventory i named the range of all white wines "white", of all red wines "red" ..

On my order sheet i created a drop down list for type and created 2 more via "indirect" .. so when you select "wine" in the first one, the 2nd one will ask you for white/red/blush/sparkling and the last drop down then for the exact item according to the range on the inventory.

Hope what i did so far is understandable ^.^

Now my problem is that i want the unit cost to be copied when an item is selected. So you select "beer" in A1, "import" in A2, "corona" in A3... and the price for corona (which is written in a cell on the inventory sheet) should show up in A4.

I attached a screen of the inventory sheet so that you can see how it is structured.

View 12 Replies View Related

Data Validation - List Box

Sep 9, 2009

Hello, could some one please help me with the following:

I have created a drop-down list for the range : C3:C65000 using Excel 2003 and Windows XP Pro. I did this using Excel's Data Validation Tool. The settings I used are as follows:

Allow: List
Source: =GROUPS
Check Box Ignore Blank is Ticked
Check Box In-cell dropdown is Ticked

GROUPS = GROUPS!$A$2:$A$29

Now all this works well, such that when I click in the cell range : C3:C65000, then a dropdown list appears - showing a list of all my groups.

However, when I click on any of the dropdown lists in this range, the width of the dropdown list is only as wide as the column. Column C has a width of 20.

What I need please is for the dropdown list to be as wide as the list of information showing in the dropdown list. If some one could please explain on how I can achieve this - that would be great.

View 7 Replies View Related

Data Validation From A List

Aug 29, 2008

I have a workbook that uses data validation from a list.
The same list is used in several of the individual sheets.
Can one use just one list for different sheets, as I'm having to create separate list for each sheet, which when an overall change needs to be made it is easy to miss one.

View 9 Replies View Related

Load Rows Data On Selection Of Data Validation List

May 28, 2014

I want to load the actual data exist in the rows by selection value from the data validation list.

E.g I have two worksheet in one excel file. One has a data activities of persons with their name like two columns i have in which one exist the name of person and second exist the activities which they perform.

On the second sheet, i made a data validation list of all the person names

Now my requirement is, when i select a person name from the list, load all the data from the 1st sheet to second sheet. Is this possible without VB code, because I want to share it on the Google sheet with my boss, where VB sheet is not supposed to work.

View 13 Replies View Related

Data Validation - How To Not Allow Items Already On A List

Jun 26, 2014

I have a cell "project titles" entered in sheet1 that populates into sheet 2 - column "Project titles".

I need to keep the values in this column unique. So can I create a data validation such a way that I won't be able to enter values into this cell that are already on the list. This way I can avoid entering duplicates.

View 3 Replies View Related

Too Much IF In Data Validation List Source

Apr 8, 2014

I try to find a solution (with or without macro) to assign different ranges named "Failure_C..." in a data validation source depending on the value of another data validation list (32 values called)

I started in the Data Validation List Source to fill in IF function but 32 inputs are not accepted and too much caracters in the source.

The formula is starting like that:

=IF(A57=$M$78,Failure_C01,IF(A57=$M$79,Failure_C02,IF(A57=$M$80,Failure_C03,
IF(A57=$M$81,Failure_C04,IF(A57=$M$82,Failure_C05,
IF(A57=$M$83,Failure_C06,IF(A57=$M$84,Failure_C07............

View 2 Replies View Related

Data Validation And List With Formula?

Jun 29, 2014

I am trying to make a simple planning sheet.

I have a workbook with 2 sheets.

1.sheet contains Columns : DayDateTimeClassTeacherCovered

2. sheet contains the availability of teachers. so all columns start with a date and the rows contain the name.

A row therefore looks like this.

7/1
Harley

Lidia
Livia
Thais
Salete
Carla

My issue: The 1.sheet has a column Teacher, now in this column I want to have a drop down list. (E2, E3, E4, E5 ... etc..) This list is depended on the date which is in column B.

Essentially I want the list to look up date in its row go to the second sheet and give me all the possible names in it. That is all.

View 11 Replies View Related

Dropdown List Using Data Validation

Feb 25, 2014

I have a list on one worksheet and I use a dropdown list from Data Validation to select the name required.

My list is so long that I would like to be able to type a letter or two to access to the name quicker instead of scrolling down.

I copied a formula that worked for someone else that put this formula in the data validation

(IF(I36<>"",OFFSET(Player,MATCH(I36&"*",Player,0)-1,,SUMPRODUCT((MID(Player,1,LEN(I36))=TEXT(I36,"0"))*1)),Player))

I do not wish to use VBA is possible.

View 1 Replies View Related

Data Validation List With A Chart

Mar 11, 2009

I am trying to create a data validation list with a bar graph. I have tried using the IF and Vlookup formula, but its not working for me. I would like to be able to select classroom # from the drop down menu and the bar graph update selected with student’s name and selected data information. I have attached the file.

View 5 Replies View Related

Creating A Data Validation List

Feb 19, 2010

I am trying to set the below table up as a validation list. I am having issues with putting it in as list as it creates a new line every time a “,” (comma) occurs. I know I can use the custom and pull the list from a table, but I can’t use this option (the reason I can’t use the table, if interested, is because the single sheet with the validation contains about ~35,000 line items that are being split up into ~200 different sheets. Each sheet is then sent as a separate email…the problem with using the table is that it would on a different spreadsheet to begin with. The email only sends one sheet. As such, it would no longer be able to reference the table).

A - Less than $25,000
B - Between $25,000 and $49,999
C - Between $50,000 and $99,999
D - Between $100,000 and $249,999
E - Between $250,000 and $499,999
F - Between $500,000 and $999,999
G - Over $1,000,000

View 5 Replies View Related

Data Validation List From Another Workbook

May 24, 2012

I have two workbooks.

Workbook1 contains validated list which is defined by a Range name and also its data is pulled from workbook2. I am unable to change the value of the validated list from drop down when i close workbook2, infact the validation drop down list do not drop at all so that can select values from it. But if i open the workbook2 i am able to change the values of the validation list.

I want the dropdown list to be active when i close the second workbook.

View 12 Replies View Related







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