I have a portion of a sheet set up as a table for user input. The top
row is fixed header information, but for the lower rows the user may
drag the table entries up and down to other rows of the table. I want
to define a name that always points to, for example, Cell D4
regardless of whether the user has done any drag, cut and paste, etc
with the table entries.

I can define a name, "CellD4" for example, with a RefersTo of either
=Indirect("Sheet1!$D$4")
or I can use
=Offset(Sheet1!$A$1, 3, 3)

Are there any performance issues or other considerations as to why I
should choose one method over the other? In one case I have several
dozen of these names that are used throughout the workbook. In
another case I want to use a similar "fixed position" reference in
several hundred (hidden) formulas on the same sheet, but not as
defined names.

Thanks,
Dan

Well, both INDIRECT and OFFSET are volatile, so, everytime your sheet is
calculated, those names will get calculated as well. One option is to use
INDEX, like:

=INDEX(Sheet1!$1:$65536,4,4)

--
Regards,

Juan Pablo González

"Dan" wrote in message
om...
I have a portion of a sheet set up as a table for user input. The top
row is fixed header information, but for the lower rows the user may
drag the table entries up and down to other rows of the table. I want
to define a name that always points to, for example, Cell D4
regardless of whether the user has done any drag, cut and paste, etc
with the table entries.

I can define a name, "CellD4" for example, with a RefersTo of either
=Indirect("Sheet1!$D$4")
or I can use
=Offset(Sheet1!$A$1, 3, 3)

Are there any performance issues or other considerations as to why I
should choose one method over the other? In one case I have several
dozen of these names that are used throughout the workbook. In
another case I want to use a similar "fixed position" reference in
several hundred (hidden) formulas on the same sheet, but not as
defined names.

Thanks,
Dan

Never mind the above... the INDEX() function calling all cells makes it
volatile, hence, similar to its INDIRECT / OFFSET competitors !

--
Regards,

Juan Pablo González

"Juan Pablo González" wrote in message
...
Well, both INDIRECT and OFFSET are volatile, so, everytime your sheet is
calculated, those names will get calculated as well. One option is to use
INDEX, like:

=INDEX(Sheet1!$1:$65536,4,4)

--
Regards,

Juan Pablo González

"Dan" wrote in message
om...
I have a portion of a sheet set up as a table for user input. The top
row is fixed header information, but for the lower rows the user may
drag the table entries up and down to other rows of the table. I want
to define a name that always points to, for example, Cell D4
regardless of whether the user has done any drag, cut and paste, etc
with the table entries.

I can define a name, "CellD4" for example, with a RefersTo of either
=Indirect("Sheet1!$D$4")
or I can use
=Offset(Sheet1!$A$1, 3, 3)

Are there any performance issues or other considerations as to why I
should choose one method over the other? In one case I have several
dozen of these names that are used throughout the workbook. In
another case I want to use a similar "fixed position" reference in
several hundred (hidden) formulas on the same sheet, but not as
defined names.

Thanks,
Dan