Question regarding dynamic range setting

[dharmik]

I have assigned range names to several different worksheets.
Can someone tell me the correct way to use offset function so tha
everytime i add more data the range automaticaly gets updated.
I tried writing the offset function but the problem is the range doe
not get updated and also the range name for which i write the offse
function disappears from the dropdown list of range names at the uppe
left corner of the sheet.
Help me with this.

thank you

Dharmi

 

[Biff]

Hi!

See this:

http://contextures.com/xlNames01.html#Dynamic

Also, only the names of static ranges will appear in the name box. When you
create a dynamic range, you're actually creating a named formula.

Biff

 

[Aladin Akyurek]

If you are on Excel 2003, you don't need range names defined by means of
a dynamic formula with OFFSET or INDEX...

Just convert the range into a list with Data|List|Create List. Then
select the range and assign a name to the selection. Whenever you add a
new record to the list, the definition will adjust itself automatically.

I have assigned range names to several different worksheets.
Can someone tell me the correct way to use offset function so that
everytime i add more data the range automaticaly gets updated.
I tried writing the offset function but the problem is the range does
not get updated and also the range name for which i write the offset
function disappears from the dropdown list of range names at the upper
left corner of the sheet.
Help me with this.

thank you

Dharmik

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.