Question regarding dynamic range setting

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

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

"dharmik" <dharmik.1sl1yh_1122055611.2803@excelforum-nospam.com> wrote in
message news:dharmik.1sl1yh_1122055611.2803@excelforum-nospam.com...
>
> 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
>
>
> --
> dharmik
> ------------------------------------------------------------------------
> dharmik's Profile:
> http://www.excelforum.com/member.php...o&userid=25463
> View this thread: http://www.excelforum.com/showthread...hreadid=389426
>

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.

dharmik wrote:
> 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.

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

"dharmik" <dharmik.1sl1yh_1122055611.2803@excelforum-nospam.com> wrote in
message news:dharmik.1sl1yh_1122055611.2803@excelforum-nospam.com...
>
> 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
>
>
> --
> dharmik
> ------------------------------------------------------------------------
> dharmik's Profile:
> http://www.excelforum.com/member.php...o&userid=25463
> View this thread: http://www.excelforum.com/showthread...hreadid=389426
>

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.

dharmik wrote:
> 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.

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

"dharmik" <dharmik.1sl1yh_1122055611.2803@excelforum-nospam.com> wrote in
message news:dharmik.1sl1yh_1122055611.2803@excelforum-nospam.com...
>
> 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
>
>
> --
> dharmik
> ------------------------------------------------------------------------
> dharmik's Profile:
> http://www.excelforum.com/member.php...o&userid=25463
> View this thread: http://www.excelforum.com/showthread...hreadid=389426
>

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.

dharmik wrote:
> 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.

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

"dharmik" <dharmik.1sl1yh_1122055611.2803@excelforum-nospam.com> wrote in
message news:dharmik.1sl1yh_1122055611.2803@excelforum-nospam.com...
>
> 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
>
>
> --
> dharmik
> ------------------------------------------------------------------------
> dharmik's Profile:
> http://www.excelforum.com/member.php...o&userid=25463
> View this thread: http://www.excelforum.com/showthread...hreadid=389426
>

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.

dharmik wrote:
> 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.

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.

dharmik wrote:
> 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.

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

"dharmik" <dharmik.1sl1yh_1122055611.2803@excelforum-nospam.com> wrote in
message news:dharmik.1sl1yh_1122055611.2803@excelforum-nospam.com...
>
> 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
>
>
> --
> dharmik
> ------------------------------------------------------------------------
> dharmik's Profile:
> http://www.excelforum.com/member.php...o&userid=25463
> View this thread: http://www.excelforum.com/showthread...hreadid=389426
>

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.

dharmik wrote:
> 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.

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

"dharmik" <dharmik.1sl1yh_1122055611.2803@excelforum-nospam.com> wrote in
message news:dharmik.1sl1yh_1122055611.2803@excelforum-nospam.com...
>
> 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
>
>
> --
> dharmik
> ------------------------------------------------------------------------
> dharmik's Profile:
> http://www.excelforum.com/member.php...o&userid=25463
> View this thread: http://www.excelforum.com/showthread...hreadid=389426
>

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.

dharmik wrote:
> 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.

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

"dharmik" <dharmik.1sl1yh_1122055611.2803@excelforum-nospam.com> wrote in
message news:dharmik.1sl1yh_1122055611.2803@excelforum-nospam.com...
>
> 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
>
>
> --
> dharmik
> ------------------------------------------------------------------------
> dharmik's Profile:
> http://www.excelforum.com/member.php...o&userid=25463
> View this thread: http://www.excelforum.com/showthread...hreadid=389426
>

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.

dharmik wrote:
> 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.

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

"dharmik" <dharmik.1sl1yh_1122055611.2803@excelforum-nospam.com> wrote in
message news:dharmik.1sl1yh_1122055611.2803@excelforum-nospam.com...
>
> 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
>
>
> --
> dharmik
> ------------------------------------------------------------------------
> dharmik's Profile:
> http://www.excelforum.com/member.php...o&userid=25463
> View this thread: http://www.excelforum.com/showthread...hreadid=389426
>

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.

dharmik wrote:
> 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.

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

"dharmik" <dharmik.1sl1yh_1122055611.2803@excelforum-nospam.com> wrote in
message news:dharmik.1sl1yh_1122055611.2803@excelforum-nospam.com...
>
> 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
>
>
> --
> dharmik
> ------------------------------------------------------------------------
> dharmik's Profile:
> http://www.excelforum.com/member.php...o&userid=25463
> View this thread: http://www.excelforum.com/showthread...hreadid=389426
>

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

"dharmik" <dharmik.1sl1yh_1122055611.2803@excelforum-nospam.com> wrote in
message news:dharmik.1sl1yh_1122055611.2803@excelforum-nospam.com...
>
> 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
>
>
> --
> dharmik
> ------------------------------------------------------------------------
> dharmik's Profile:
> http://www.excelforum.com/member.php...o&userid=25463
> View this thread: http://www.excelforum.com/showthread...hreadid=389426
>

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.

dharmik wrote:
> 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.

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

"dharmik" <dharmik.1sl1yh_1122055611.2803@excelforum-nospam.com> wrote in
message news:dharmik.1sl1yh_1122055611.2803@excelforum-nospam.com...
>
> 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
>
>
> --
> dharmik
> ------------------------------------------------------------------------
> dharmik's Profile:
> http://www.excelforum.com/member.php...o&userid=25463
> View this thread: http://www.excelforum.com/showthread...hreadid=389426
>

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.

dharmik wrote:
> 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.

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.

dharmik wrote:
> 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.

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

"dharmik" <dharmik.1sl1yh_1122055611.2803@excelforum-nospam.com> wrote in
message news:dharmik.1sl1yh_1122055611.2803@excelforum-nospam.com...
>
> 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
>
>
> --
> dharmik
> ------------------------------------------------------------------------
> dharmik's Profile:
> http://www.excelforum.com/member.php...o&userid=25463
> View this thread: http://www.excelforum.com/showthread...hreadid=389426
>

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

"dharmik" <dharmik.1sl1yh_1122055611.2803@excelforum-nospam.com> wrote in
message news:dharmik.1sl1yh_1122055611.2803@excelforum-nospam.com...
>
> 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
>
>
> --
> dharmik
> ------------------------------------------------------------------------
> dharmik's Profile:
> http://www.excelforum.com/member.php...o&userid=25463
> View this thread: http://www.excelforum.com/showthread...hreadid=389426
>

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.

dharmik wrote:
> 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.

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

"dharmik" <dharmik.1sl1yh_1122055611.2803@excelforum-nospam.com> wrote in
message news:dharmik.1sl1yh_1122055611.2803@excelforum-nospam.com...
>
> 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
>
>
> --
> dharmik
> ------------------------------------------------------------------------
> dharmik's Profile:
> http://www.excelforum.com/member.php...o&userid=25463
> View this thread: http://www.excelforum.com/showthread...hreadid=389426
>

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.

dharmik wrote:
> 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.