Sometimes if you are referring to a cell in another sheet (in the same
workbook) Excel decides for no apparent reason that all your references
should be absolute.

Specifically I was referencing cells a list on another worksheet, and when
you do that you want to use relative references. However Excel had for some
reason made up its mind that all references should be absolute.

Of course this is easy to correct by hitting F4, but it is inefficient an
extremely irritating.

I don't know what kind of a geek of a programmer in Microsoft that came up
with this "helpful" feature of "automatic absolute referencing", but if the
aim was to irritate us users he succeeded with me.

Anyway, do anyone know how to make Excel RELATIVE references to another
sheet default again.

I don't think I've ever seen excel do this in a formula in a cell.

Was that what you were doing or were you entering something into a dialog?

If it was a dialog, then which one (but I imagine you won't be able to change
excel's behavior for that).

Espen wrote:

Sometimes if you are referring to a cell in another sheet (in the same
workbook) Excel decides for no apparent reason that all your references
should be absolute.

Specifically I was referencing cells a list on another worksheet, and when
you do that you want to use relative references. However Excel had for some
reason made up its mind that all references should be absolute.

Of course this is easy to correct by hitting F4, but it is inefficient an
extremely irritating.

I don't know what kind of a geek of a programmer in Microsoft that came up
with this "helpful" feature of "automatic absolute referencing", but if the
aim was to irritate us users he succeeded with me.

Anyway, do anyone know how to make Excel RELATIVE references to another
sheet default again.

--

Dave Peterson

Ok, perhaps my explanation was not too clear, so here is what sometimes
happens.

Usually when you entering "=" and selects a cell on a different sheet the
result is "='The name of the other sheet'A1" for exampel. However, sometimes
after you have been working a little, Excel starts to automatically adds
$-signs, and so when you select a cell from another worksheet you will read
for example "='The name of the other sheet'$A$1", and you have to press F4 to
get rid of the $'s.

It is very annoying, and it comes and goes unpredictable. Perhaps it is just
a little bug in the application ...


"Dave Peterson" wrote:

I don't think I've ever seen excel do this in a formula in a cell.

Was that what you were doing or were you entering something into a dialog?

If it was a dialog, then which one (but I imagine you won't be able to change
excel's behavior for that).

Espen wrote:

Sometimes if you are referring to a cell in another sheet (in the same
workbook) Excel decides for no apparent reason that all your references
should be absolute.

Specifically I was referencing cells a list on another worksheet, and when
you do that you want to use relative references. However Excel had for some
reason made up its mind that all references should be absolute.

Of course this is easy to correct by hitting F4, but it is inefficient an
extremely irritating.

I don't know what kind of a geek of a programmer in Microsoft that came up
with this "helpful" feature of "automatic absolute referencing", but if the
aim was to irritate us users he succeeded with me.

Anyway, do anyone know how to make Excel RELATIVE references to another
sheet default again.

--

Dave Peterson

I've seen this happen with different workbooks (like you wrote), but I don't
recall ever seeing this within the same workbook.

So either I'm lucky or it's gonna start real soon <vbg.

You may want to post the version of excel that you're using. Maybe someone can
confirm/post a workaround.

Espen wrote:

Ok, perhaps my explanation was not too clear, so here is what sometimes
happens.

Usually when you entering "=" and selects a cell on a different sheet the
result is "='The name of the other sheet'A1" for exampel. However, sometimes
after you have been working a little, Excel starts to automatically adds
$-signs, and so when you select a cell from another worksheet you will read
for example "='The name of the other sheet'$A$1", and you have to press F4 to
get rid of the $'s.

It is very annoying, and it comes and goes unpredictable. Perhaps it is just
a little bug in the application ...

"Dave Peterson" wrote:

I don't think I've ever seen excel do this in a formula in a cell.

Was that what you were doing or were you entering something into a dialog?

If it was a dialog, then which one (but I imagine you won't be able to change
excel's behavior for that).

Espen wrote:

Sometimes if you are referring to a cell in another sheet (in the same
workbook) Excel decides for no apparent reason that all your references
should be absolute.

Specifically I was referencing cells a list on another worksheet, and when
you do that you want to use relative references. However Excel had for some
reason made up its mind that all references should be absolute.

Of course this is easy to correct by hitting F4, but it is inefficient an
extremely irritating.

I don't know what kind of a geek of a programmer in Microsoft that came up
with this "helpful" feature of "automatic absolute referencing", but if the
aim was to irritate us users he succeeded with me.

Anyway, do anyone know how to make Excel RELATIVE references to another
sheet default again.

--

Dave Peterson


--

Dave Peterson

Yes, that seems like a good idea. My version is

Excel 2003 (11.6355.6360) SP1

"Dave Peterson" wrote:

I've seen this happen with different workbooks (like you wrote), but I don't
recall ever seeing this within the same workbook.

So either I'm lucky or it's gonna start real soon <vbg.

You may want to post the version of excel that you're using. Maybe someone can
confirm/post a workaround.

Espen wrote:

Ok, perhaps my explanation was not too clear, so here is what sometimes
happens.

Usually when you entering "=" and selects a cell on a different sheet the
result is "='The name of the other sheet'A1" for exampel. However, sometimes
after you have been working a little, Excel starts to automatically adds
$-signs, and so when you select a cell from another worksheet you will read
for example "='The name of the other sheet'$A$1", and you have to press F4 to
get rid of the $'s.

It is very annoying, and it comes and goes unpredictable. Perhaps it is just
a little bug in the application ...

"Dave Peterson" wrote:

I don't think I've ever seen excel do this in a formula in a cell.

Was that what you were doing or were you entering something into a dialog?

If it was a dialog, then which one (but I imagine you won't be able to change
excel's behavior for that).

Espen wrote:

Sometimes if you are referring to a cell in another sheet (in the same
workbook) Excel decides for no apparent reason that all your references
should be absolute.

Specifically I was referencing cells a list on another worksheet, and when
you do that you want to use relative references. However Excel had for some
reason made up its mind that all references should be absolute.

Of course this is easy to correct by hitting F4, but it is inefficient an
extremely irritating.

I don't know what kind of a geek of a programmer in Microsoft that came up
with this "helpful" feature of "automatic absolute referencing", but if the
aim was to irritate us users he succeeded with me.

Anyway, do anyone know how to make Excel RELATIVE references to another
sheet default again.

--

Dave Peterson


--

Dave Peterson

I also use xl2003.

The only time I've seen xl use those $ signs when building formulas is when I've
pointed to different workbooks.

I've never seen it happen when I point at a different worksheet within the same
workbook.

Could that be it?

(I don't have any other guesses, though.)

Espen wrote:

Yes, that seems like a good idea. My version is

Excel 2003 (11.6355.6360) SP1

"Dave Peterson" wrote:

I've seen this happen with different workbooks (like you wrote), but I don't
recall ever seeing this within the same workbook.

So either I'm lucky or it's gonna start real soon <vbg.

You may want to post the version of excel that you're using. Maybe someone can
confirm/post a workaround.

Espen wrote:

Ok, perhaps my explanation was not too clear, so here is what sometimes
happens.

Usually when you entering "=" and selects a cell on a different sheet the
result is "='The name of the other sheet'A1" for exampel. However, sometimes
after you have been working a little, Excel starts to automatically adds
$-signs, and so when you select a cell from another worksheet you will read
for example "='The name of the other sheet'$A$1", and you have to press F4 to
get rid of the $'s.

It is very annoying, and it comes and goes unpredictable. Perhaps it is just
a little bug in the application ...

"Dave Peterson" wrote:

I don't think I've ever seen excel do this in a formula in a cell.

Was that what you were doing or were you entering something into a dialog?

If it was a dialog, then which one (but I imagine you won't be able to change
excel's behavior for that).

Espen wrote:

Sometimes if you are referring to a cell in another sheet (in the same
workbook) Excel decides for no apparent reason that all your references
should be absolute.

Specifically I was referencing cells a list on another worksheet, and when
you do that you want to use relative references. However Excel had for some
reason made up its mind that all references should be absolute.

Of course this is easy to correct by hitting F4, but it is inefficient an
extremely irritating.

I don't know what kind of a geek of a programmer in Microsoft that came up
with this "helpful" feature of "automatic absolute referencing", but if the
aim was to irritate us users he succeeded with me.

Anyway, do anyone know how to make Excel RELATIVE references to another
sheet default again.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

http://tutorialway.com/use-absolute-...ence-in-excel/
helpful site