HOW TO EXTEND A NAMED RANGE

R

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
 
D

David Billigmeier

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

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top