Depending dropbox

[Judith_Chao]

Hi

I need to make a Dropbox who populate values depending other Dropbox. For example at the example file I have two sheets, one for the values (Hoja2) and the other for the data (Hoja1). I want to limit the input data to the value list:

- At column Country I define a dinamic range to populate a Dropbox with all the data at the column A at the value list. This I have

- At column City I need that depending on the value I select at column Country shows at one Dropbox all the values at the row of this country I have at values sheets. This values can change, adding or remove values. Is there any posibility to do this with formula and without using range name? I'm think on Offset or Vlook formula but I can't figure out how to use.

At the example If I select EEUU at the column City I nedd it apears at the combo in City column all the values I may have at the row 3 (excluding column A), and set it dinamically (I can delete or add values at the row).

Thanks in advance for any help

[FDibbins]

See if you can adapt the attached to your needs.

It used named ranges for each level and sub-levels
It used INDIRECT() to pull in the sub-levels

let me know how you make out please

[Judith_Chao]

See if you can adapt the attached to your needs.

It used named ranges for each level and sub-levels
It used INDIRECT() to pull in the sub-levels

let me know how you make out please

Thank you for the answer but, it's posible to do the same without using range name, i don't know with vlook formula or similar?.

Also the range names are not dinamics, I need the adapt to the data I put, and change automatically if i added some value or remove it. If i'm correct the range names defined at your file are static, you must change ir manually if changes the values.

Any way, many thanks for the answer.

[FDibbins]

In order to use the cell contents of 1 DD to ID the contents for the next DD, you need to use INDIRECT - it's the only (formula) way to use text as a reference in a formula.
You also need to use named ranges, to ID the range/contents that you need to access

To make the ranges dynamic is not that much of an issue.

Go to: Insert>Name>Define and in the Names in workbook box type any one word name (I will use MyRange) the only part that will change is the formula we place in the Refers to box.
If you are using numerics...
In the Refers to box type: =OFFSET($A$1,0,0,COUNT($A:$A),1)

for text/numeric...
In the Refers to box type: =OFFSET($A$1,0,0,COUNTA($A:$A),1)

There are a few others but that should help

[FDibbins]

Deleted my duplicate post

[Judith_Chao]

My problem is if I use offset at the first drop (I.e. Country column) it gives me erro le if I try to use indirect at the City Dropbox even the range name was correct. If I use static ranges gives me no problem but with dynamics it give me error.

For that I ask to make this without range names in order to be able to define ranges dinamically

[JohnTopley]

See here ...


http://www.contextures.com/xlDataVal02.html#Dynamic

Attached has an example using your data.

Create named ranges for titles in B1:E1 i.e EEUU, Spain, France, Deutschland

Then select each column e.g. column B and call it "EEUUCol" etc

in Sheet 1 B5

DV

=List

refers to: =OFFSET(INDIRECT($A5),1,0,COUNTA(INDIRECT($A$5&"Col")),1)

[Judith_Chao]

See here ...


http://www.contextures.com/xlDataVal02.html#Dynamic

Attached has an example using your data.

Create named ranges for titles in B1:E1 i.e EEUU, Spain, France, Deutschland

Then select each column e.g. column B and call it "EEUUCol" etc

in Sheet 1 B5

DV

=List

refers to: =OFFSET(INDIRECT($A5),1,0,COUNTA(INDIRECT($A$5&"Col")),1)

I see it and it works tank you .... But I prefer one option without names.

Only for knowing is possible to do that withou name ranges, I don't know if using vlook or index can substitute the name range ... is this possible ? I have spaces and signs at the names and I can not use them at name range so I need another method


Many thanks for your answer

[JohnTopley]

This is the only way to do it.

What is your objection to named ranges?

[Judith_Chao]

This is the only way to do it.

What is your objection to named ranges?

That I have names with espaces and signs that I cannot use reference. Example, I may have a range "Axa security's" and I need to appear like that at the first column and then select some of their policy's at the second combo. I can't create a range name "axa security's".

I have a userform to search the data and no problem with vba I made a depending combos in order to filter the data. But now I want to use data validation at the sheet I have the database so I use drops but with range names I must change half of my list of values because they had spaces and other signs .... maybe I can use the same code from the userform at the drop at the sheet but I don't know how...

[JohnTopley]

You can get round the spaces and other characters::

"Axa_security" is automatically generated As a named range if you have a heading of "Axa security" remove the apostrophe and "S"

With A1= "Axa Security"

then you can use =INDIRECT(SUBSTITUTE(A1," ","_"))

which replaces the blanks in A1 drop down with"_" to match named ranges.

[JohnTopley]

See example of "United Kingdom" in Drop down

Post a sample of your data with "special" characters.

[Judith_Chao]

Thank you

I check when I arrive home ... Only for confirm, the column I must use to name the ranges it can appear the symbols - / . and blank spaces. I hope Your file solve all of it ....

[Judith_Chao]

Thank you

I check when I arrive home ... Only for confirm, the column I must use to name the ranges it can appear the signs : - / . And blank spaces. I hope Your file solve all of it ....

[JohnTopley]

As I requested earlier. post a sample of your data with "special" characters.

Example:

A1: abc-gfh / 123.xyz

Using =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"/","_")," ","_"),"-","_"),".","_") we get

abc_gfh___123_xyz

Using =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"/","_")," ","_"),"-","_") we get

abc_gfh___123.xyz

Both the highlighted are valid named ranges

You could put the SUBSTITUTION formulae in a "helper" cell and reference the "helper" cell with INDIRECT.

OR

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"/","")," ",""),"-","_")

to give

abc_gfh123.xyz

[Judith_Chao]

As I requested earlier. post a sample of your data with "special" characters.

Sorry I was at work with the phone so I can't attach any file... examples of the special characters are:
Generally secure - AGM
M.N.G Absolute
Axa Security / M. N. G.

This is a resume of the possibilities you can find ... into 4 hours I arrive home and I can upload a file. Sorry for the example of countries it was the easy way to create a test file early this morning in the rush. The data I must use are name of secure companies and his different policys so I can have more or less anything of name. Is for that I prefer not use names and use or similar formula (or even the same code I use for the combo is at the user form I created to search the data and who admits all the values)...

Thanks for you replay, if you need an example file I uploaded it when arrive home

[Tony Valko]

Is this what you're trying to do?

[JohnTopley]

See Tony's method of using OFFSET which removes the need for named ranges: only requirement is data must be sorted ("countries" grouped together) but I don't think that is a major issue.

[Judith_Chao]

See Tony's method of using OFFSET which removes the need for named ranges: only requirement is data must be sorted ("countries" grouped together) but I don't think that is a major issue.

Now I created a test file more exactly than the other. At Mutuas Sheet you can see an example about haw I speak. I make the first column List with code, it rest only makes the depending one, second one. I have a code at sheet data to fill the columna A and C from sheet data with a dynamic list (from values of column A from Pruebas and Mutuas). It rest make it for column B and D from the shett whic values depends at first column as you can see at the sheet pruebas and Mutuas (the rows values dependes of the values selected at first column).

I hope you can provide me any guide with fornula (vlook, Index, Match, etc.) that you don't need to use names. At the user form I have been able to do without problem....

Also I check and I can't use Ofset range at column B or D if I use Indirect to get colum A or C values. I only can use static ranges with indirect formula

[JohnTopley]

See "Data (2)" columns C & D

DV in D

=OFFSET(List!$H$1,MATCH($C7,List!G:G,0)-1,,COUNTIF(List!G:G,$C7))

Data for DV is in columns G and H in "List"

[Judith_Chao]

See "Data (2)" columns C & D

DV in D

=OFFSET(List!$H$1,MATCH($C7,List!G:G,0)-1,,COUNTIF(List!G:G,$C7))

Data for DV is in columns G and H in "List"

Thank you ... I see at you example I must make another table to use the formula you give me. One question, the table must be ordered?