HOW TO EXTEND A NAMED RANGE

[ramana]

Hi Everybody,

I have Named a range(say from A1 to A5),and B1to B5, C1toC5 for
dependent drop down list. and for Vlookup. now I wanted to include some
more names in the names in the named ranges. How can extend
rows/columns for these named ranges.

Thanks and Regards

Ramana

 

[Anne Troy]

Just select and name the range again. You are not even asked if you want to
overwrite it, which I think is not a good thing, but...that's the way it is.
See:
http://www.officearticles.com/excel/named_ranges_in_microsoft_excel.htm
************
Anne Troy
www.OfficeArticles.com

 

[Guest]

You can directly edit the cell references by going Insert->Name->Define and
making the changes in there.

 

[Max]

.. How can extend rows/columns for these named ranges.

Another option is to use dynamic ranges

Try Debra's nice coverage on this at her:
http://www.contextures.com/xlNames01.html#Dynamic

 

[Arvi Laanemets]

Hi

You also can define the named range as dynamic one. There are 2 different
types of dynamic named ranges:
- The range adjusts automatically, whenever rows/columns are added to
source table. As rule, the table must be contignous. An example here
MyRange=OFFSET(Sheet1!$A$1,1,,COUNTA(Sheet1!$A:$A)-1,5)
, which returns the whole contignous table (the headers in row 1 excluded)
from sheet1
- The range depends on cell with reference to this range. An example
here
MyRange=INDEX(Sheet1!$A$2:$F$100,,MATCH(C$1,Sheet1!$A$1:$F$1,0))
, which returns a column from table on sheet 1, which header is same as
column header for cell with calling formula. I.e. for different calling
cells the returned range can be different.

Of course you can define also a named range, where both techniques are used
simultanously.

 

[ramana]

Thanks a lot for all of you for the different ways you suggested.

Regards

Ramana