Problem with named ranges in Excel 1

[FarzanaSaleem]

I used Named Ranges for Data Validation in Excel as follows: (To do this, I took the help from thread thread68-608325. To explain my issue, I am using same sample values).

Sheet1:
Column A
Master
Apple
Orange
Banana
Lemon
Kiwi
Peach
Pear
Plum

Column B
Apple Detail
SelectionBasedOnApple
SelectionBasedOnApple
SelectionBasedOnApple
SelectionBasedOnApple
SelectionBasedOnApple
SelectionBasedOnApple

Column C
Orange Detail
SelectionBasedOnOrange
SelectionBasedOnOrange
SelectionBasedOnOrange
SelectionBasedOnOrange
SelectionBasedOnOrange

I defined 3 named ranges as follows:
Master refers to =OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)
Apple refers to =Sheet1!$B$2:$B$6
Orange refers to =Sheet1!$C$2:$C$6

Sheet2:
Column A
A1 = Master
A2 = Data->Validation->List (Source =Master)

Column B
B1 = Detail
B2 = Data->Validation->List (Source =Indirect(A2))

This works fine. Problem arises, when I try to use Dynamic Ranges for Column B or Column C in Sheet1:
For example, I changed Apple reference as:
Apple refers to =OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B:$B)-1,1)

After that, when I select Orange from Master list in Sheet2, SelectionBasedOnOrange is displayed in Detail List, but when I select Apple, NOTHING IS DISPLAYED IN DETAIL LIST.

 

[xlbo]

You can't use INDIRECT with dynamic range names. Indirect converts a STRING to a RANGE. In the case of a dynamic name, the string is
=OFFSET(.......
which cannot be converted to a range. Indirect is looking for something like:
=$A$1:$A$10

if you know what column your range will be in and what cell it starts in, you can create the range addresses in a cell and reference that with Indirect..

Rgds, Geoff
[blue]Experience is something you don't get until just after you need it[/blue]
We want to help [red]you[/red] Help us by reading this FAQ 1st faq222-2244

 

[FarzanaSaleem]

Thanks Geoff

you can create the range addresses in a cell and reference that with Indirect..

Geoff,

Would you please understand the above line a bit more. I could not understand this point

Regards

 

[xlbo]

Ok - lets say you have a list of "Orange" details
This list starts in Cell E1.
In cell F1 (or any blank cell), enter

="$E$1:$E$" & counta(E:E)

Name cell F1 (or whichever cell you have chosen) "Orange"

You should now be able to use =INDIRECT(Orange)
to reference the list
Or, more to the point, you can use
=INDIRECT(A1)
where A1 contains your master data validation cell of:
Apple
Orange
Banana
Lemon
Kiwi
Peach
Pear
Plum

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[FarzanaSaleem]

I proceeded as follows:

1. Shifted List of "Orange Details" to Column E (E1:E5 = SelectionBasedOnOrange)
2. In Cell F1, typed ="$E$1:$E$" & counta(e:e) (For this example, $E$1:$E$5 appeared in F1
3. Insert -> Name -> Define, and named Cell F1 as Orange
4. In Sheet2, Column B, Cell B2:
Data -> Validation -> List (Source = Indirect(A2)
5. Now when I selected Orange in A2, B2 Combo box showed $E$1:$E$5 AND NOT THE LIST CORRESPONDING TO THIS RANGE

 

[xlbo]

apologies - the source for the list should be
=Indirect(Indirect(A1))

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[FarzanaSaleem]

Geoff,

I tried this but B2 Combo Box is not displaying the list. Combo box drops down, but now nothing displays.

 

[xlbo]

You need to have selected ORANGE in A1 1st
Tested this and it works

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[FarzanaSaleem]

Geoff,

I am still unable to do it correctly. May be I am making some mistake.

Is it possible that I could email the file to you for your review.

 

[xlbo]

yup - Geoff dot Barraclough At Punchpubs.co.uk

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[xlbo]

I think this is now sorted. Farzana was using 2 sheets - one for the validation, one for the lists. In this instance, the formula in F1 (on another sheet) would be
="'SheetName'!$E$1:$E$" & counta(e:e)

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[FarzanaSaleem]

Thanks Geoff.

Now it is working perfectly.

Regards

 

[FarzanaSaleem]

I am facing one problem in this approach of data validation. Values in the master list can contain only underscores among the words. No spaces and hyphens can be used.

For example, if I add Water-melon in the master list:

Master (Column A, Sheet1)
Apple
Orange
Banana
Lemon
Kiwi
Peach
Pear
Plum
Water-melon

Water-melon Detail (Column C, Sheet1)
SelectionBasedOnWater-melon
SelectionBasedOnWater-melon
SelectionBasedOnWater-melon
SelectionBasedOnWater-melon
SelectionBasedOnWater-melon

F2 = "'Sheet1'!$C$2:$C$" & COUNTA(C:C)

When I try to define name of F2 as Water-melon, the following message is displayed:

That name is not valid.

 

[xlbo]

Yup - named ranges can't have spaces in them.

Keep the spaces in Water Melon and change the data validation formula to:

=INDIRECT(INDIRECT(SUBSTITUTE(A1," ","")))

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question