Creating dynamic ranges which are named.

In Office 2007, I want to create a named range on one tab and use it as the
list in data validation in another tab.

Imagine the following:

1) one work book with 2 tabs.
2) Tab1 and Tab2 are the respectively named tabs.
3) In Tab2 I have the data range and in Tab1 I want to create a drop down
box which will display the values in the data range.
4) In Tab2 the data range is in, say B2:B20. At any stage, I could add more
data below B20 and I want that those values are also thereafter provided as
options in the dropdown box.
5) Finally let us say I want this dropdown box in Tab1 in Cell D6.

This is how I do:

a) I create a named range using the Excel 2003 sequence of Alt-I/N/D. In
the range I enter the formula:
=INDIRECT(CONCATENATE("'Tab2"!$B2:$B",COUNTA($B$2: $B$1001)+2))
b) THIS DOES NOT WORK IN OFFICE 2007 WHILE IT WORKED IN OFFICE 2003!!

Can someone explain where I am going wrong? Any add-in I need to install?

Thanks

Uttam

Try it like this...

Assuming the data range on Tab2 is a contiguous range (no empty cells within
the range).

Create this defined name...

RibbonFormulas tabDefined NamesDefine Name
Name: MyList (or whatever name you want)
Refers to:

='Tab2'!$B$2:INDEX('Tab2'!$B$2:$B$100,COUNTA('Tab2 '!$B$2:$B$100))

Adjust for a reasonable end of range B100.

OK out

As the source for your drop down on Tab1 use:

=MyList

--
Biff
Microsoft Excel MVP


"U0107" wrote in message
...
In Office 2007, I want to create a named range on one tab and use it as
the
list in data validation in another tab.

Imagine the following:

1) one work book with 2 tabs.
2) Tab1 and Tab2 are the respectively named tabs.
3) In Tab2 I have the data range and in Tab1 I want to create a drop down
box which will display the values in the data range.
4) In Tab2 the data range is in, say B2:B20. At any stage, I could add
more
data below B20 and I want that those values are also thereafter provided
as
options in the dropdown box.
5) Finally let us say I want this dropdown box in Tab1 in Cell D6.

This is how I do:

a) I create a named range using the Excel 2003 sequence of Alt-I/N/D. In
the range I enter the formula:
=INDIRECT(CONCATENATE("'Tab2"!$B2:$B",COUNTA($B$2: $B$1001)+2))
b) THIS DOES NOT WORK IN OFFICE 2007 WHILE IT WORKED IN OFFICE 2003!!

Can someone explain where I am going wrong? Any add-in I need to install?

Thanks

Uttam