Dynamic named ranges (ms excel)

 

 

Named ranges are among the most powerful features of Excel, especially when used as the source range for list controls, PivotTables, or charts. A problem arises, however, when the contents of a list change often. It would be a problem to have to redefine your named ranges everytime a table has records added or removed. The solution is to create a range that will automatically adjust based on the number of items in the list.

 

 

First, create a list in column A of a worksheet.

 

 

If you are working on a version EARLIER than Excel 2007 :

 

 

From the worksheet’s Insert menu choose Names then the Define…. Enter a name for your new range, such as MySheet!rngDynamic. Then, in the Refers to: box, enter the following:

 

 

=OFFSET(MySheet!$A$1,0,0,COUNTA(MySheet!$A:$A),1)

 

 

The user interface changed with Excel 2007. So instead, from the Formulas menu go to theDefine Names group and create this named range fron the Define Name command.

 

 

How It Works:

 

 

The first argument for the OFFSET function is the cell on which you want to anchor it. Everything else will be set relative the this cell address. Typically, you will want it to be either the header for the first field in your source data table or its first record.

 

 

Click Here TO Continue Reading

Replies (7)

Thanks a lot sir..............

Dear Sir,

You can attached Example File in excel formet with this Massage.

 

Originally posted by : Ram Avtar Singh

Dear Sir,

You can attached Example File in excel formet with this Massage.

 

hi sir one example file is attached,i have shown its use in Vlookup Formula

nice & useful

thanks ankur ji again....very valuable sharing once again!!!

Dear sir,

Thanks for sharing this type of file so all member can learn easily by example.

 

Originally posted by : Ram Avtar Singh

Dear sir,

Thanks for sharing this type of file so all member can learn easily by example.

 

 

 

sir, i have learned this thing from you.....

Leave a Reply

Your are not logged in . Please login to post replies

Click here to Login / Register  

Company
Featured 27 October 2026
semi-qualified CA

Saini Pati Shah & Co LLP, Chartered Accountants

Mumbai

CA Final

View Details
Company
10 November 2026
FP&A Executive

Gujarat Guardian

Ankleshwar

CA Final

View Details
Company
ARTICLESHIP 18 November 2026
Articled Assistant

A.Singhi & Co

Kolkata

CA Inter

View Details
Company
Featured ARTICLESHIP 31 October 2026
CA Articleship

apricus india

Navi Mumbai

CA Inter

View Details
Company
Featured ARTICLESHIP 28 October 2026
CA- Inter and Articles

N N V Satish & Co.,

Hyderabad

CA Inter

View Details
Company
Featured 29 October 2026
Accounts Executive

Synergy Keystone

Chennai

B.Com

View Details
Company
16 November 2026
Assistant Article

ZAK & Co. Chartered Accountants

Bengaluru

CA Inter

View Details
Company
30 October 2026
Asst Manager Accounts

Goodwin Pumps India Pvt Ltd

Chengalpattu

CA Inter

View Details