Creating dynamic ranges which are named.
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
|