Name Ranges
 

I have a worksheet where I use multiple named ranges (Excel 2003
)
=OFFSET(Sheet1!$G$1,0,0,COUNTA(Sheet1!$A:$A),1)

When I remove old data from Sheet1, with the exception of the column
headers, row 1, and add new data I have to update all my name ranges and it
is always the cell it references.

In the example above I would have to update the $G$1 because it would read
after I delete the data and add new:
=OFFSET(Sheet1!$REF!,0,0,COUNTA(Sheet1!$A:$A),1)

Is there a way to avoid having to update these every time I delete the my
old data and add new, it is time consuming to update all the ranges and they
are needed

Name Ranges
 

One way:

=OFFSET(INDIRECT("Sheet1!G1"),,,COUNTA(Sheet1!$A:$ A))

Note that that will *always* refer to Sheet1!G1. If you inserted new columns
or rows and G1 was no longer the "anchor" cell that formula *won't* update to
reflect the action.

--
Biff
Microsoft Excel MVP


"Jen_T" wrote:

I have a worksheet where I use multiple named ranges (Excel 2003
)
=OFFSET(Sheet1!$G$1,0,0,COUNTA(Sheet1!$A:$A),1)

When I remove old data from Sheet1, with the exception of the column
headers, row 1, and add new data I have to update all my name ranges and it
is always the cell it references.

In the example above I would have to update the $G$1 because it would read
after I delete the data and add new:
=OFFSET(Sheet1!$REF!,0,0,COUNTA(Sheet1!$A:$A),1)

Is there a way to avoid having to update these every time I delete the my
old data and add new, it is time consuming to update all the ranges and they
are needed

Name Ranges
 

Do I still include the,0,0

For example:
=OFFSET(INDIRECT("Sheet1!$G$1"),0,0,COUNTA(Sheet1! $A:$A),1)

"T. Valko" wrote:

One way:

=OFFSET(INDIRECT("Sheet1!G1"),,,COUNTA(Sheet1!$A:$ A))

Note that that will *always* refer to Sheet1!G1. If you inserted new columns
or rows and G1 was no longer the "anchor" cell that formula *won't* update to
reflect the action.

--
Biff
Microsoft Excel MVP


"Jen_T" wrote:

I have a worksheet where I use multiple named ranges (Excel 2003
)
=OFFSET(Sheet1!$G$1,0,0,COUNTA(Sheet1!$A:$A),1)

When I remove old data from Sheet1, with the exception of the column
headers, row 1, and add new data I have to update all my name ranges and it
is always the cell it references.

In the example above I would have to update the $G$1 because it would read
after I delete the data and add new:
=OFFSET(Sheet1!$REF!,0,0,COUNTA(Sheet1!$A:$A),1)

Is there a way to avoid having to update these every time I delete the my
old data and add new, it is time consuming to update all the ranges and they
are needed

Name Ranges
 

You can if you want to.

The way I wrote it:

=OFFSET(INDIRECT("Sheet1!G1"),,,COUNTA(Sheet1!$A:$ A))

Those arguments are empty so they default to 0.

These 2 formulas do *exactly* the same thing:

=OFFSET(INDIRECT("Sheet1!$G$1"),0,0,COUNTA(Sheet1! $A:$A),1)

=OFFSET(INDIRECT("Sheet1!G1"),,,COUNTA(Sheet1!$A:$ A))


--
Biff
Microsoft Excel MVP


"Jen_T" wrote:

Do I still include the,0,0

For example:
=OFFSET(INDIRECT("Sheet1!$G$1"),0,0,COUNTA(Sheet1! $A:$A),1)

"T. Valko" wrote:

One way:

=OFFSET(INDIRECT("Sheet1!G1"),,,COUNTA(Sheet1!$A:$ A))

Note that that will *always* refer to Sheet1!G1. If you inserted new columns
or rows and G1 was no longer the "anchor" cell that formula *won't* update to
reflect the action.

--
Biff
Microsoft Excel MVP


"Jen_T" wrote:

I have a worksheet where I use multiple named ranges (Excel 2003
)
=OFFSET(Sheet1!$G$1,0,0,COUNTA(Sheet1!$A:$A),1)

When I remove old data from Sheet1, with the exception of the column
headers, row 1, and add new data I have to update all my name ranges and it
is always the cell it references.

In the example above I would have to update the $G$1 because it would read
after I delete the data and add new:
=OFFSET(Sheet1!$REF!,0,0,COUNTA(Sheet1!$A:$A),1)

Is there a way to avoid having to update these every time I delete the my
old data and add new, it is time consuming to update all the ranges and they
are needed

Name Ranges
 

I only get that error if I delete G1 (row 1 or column G)

How are you deleting the old data?

Maybe clearing the range (or clearing contents) would be ok.

Jen_T wrote:

I have a worksheet where I use multiple named ranges (Excel 2003
)
=OFFSET(Sheet1!$G$1,0,0,COUNTA(Sheet1!$A:$A),1)

When I remove old data from Sheet1, with the exception of the column
headers, row 1, and add new data I have to update all my name ranges and it
is always the cell it references.

In the example above I would have to update the $G$1 because it would read
after I delete the data and add new:
=OFFSET(Sheet1!$REF!,0,0,COUNTA(Sheet1!$A:$A),1)

Is there a way to avoid having to update these every time I delete the my
old data and add new, it is time consuming to update all the ranges and they
are needed


--

Dave Peterson

Name Ranges
 

I highlight all the data from a2 down and delete those rows. Then when I
paste the new data in row a2I get that error.

"Dave Peterson" wrote:

I only get that error if I delete G1 (row 1 or column G)

How are you deleting the old data?

Maybe clearing the range (or clearing contents) would be ok.

Jen_T wrote:

I have a worksheet where I use multiple named ranges (Excel 2003
)
=OFFSET(Sheet1!$G$1,0,0,COUNTA(Sheet1!$A:$A),1)

When I remove old data from Sheet1, with the exception of the column
headers, row 1, and add new data I have to update all my name ranges and it
is always the cell it references.

In the example above I would have to update the $G$1 because it would read
after I delete the data and add new:
=OFFSET(Sheet1!$REF!,0,0,COUNTA(Sheet1!$A:$A),1)

Is there a way to avoid having to update these every time I delete the my
old data and add new, it is time consuming to update all the ranges and they
are needed


--

Dave Peterson

Name Ranges
 

I still can't duplicate this.

I couldn't break it no matter what I tried. Do you use merged cells?

I tried a couple variations and it still worked, but maybe I didn't try merged
cells like you're using?

(Merged cells can be trouble!)

Jen_T wrote:

I highlight all the data from a2 down and delete those rows. Then when I
paste the new data in row a2I get that error.

"Dave Peterson" wrote:

I only get that error if I delete G1 (row 1 or column G)

How are you deleting the old data?

Maybe clearing the range (or clearing contents) would be ok.

Jen_T wrote:

I have a worksheet where I use multiple named ranges (Excel 2003
)
=OFFSET(Sheet1!$G$1,0,0,COUNTA(Sheet1!$A:$A),1)

When I remove old data from Sheet1, with the exception of the column
headers, row 1, and add new data I have to update all my name ranges and it
is always the cell it references.

In the example above I would have to update the $G$1 because it would read
after I delete the data and add new:
=OFFSET(Sheet1!$REF!,0,0,COUNTA(Sheet1!$A:$A),1)

Is there a way to avoid having to update these every time I delete the my
old data and add new, it is time consuming to update all the ranges and they
are needed


--

Dave Peterson


--

Dave Peterson