Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Espen
 
Posts: n/a
Default Excel insits that I use absolute cell references

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.
  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #3   Report Post  
Espen
 
Posts: n/a
Default

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

  #4   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #5   Report Post  
Espen
 
Posts: n/a
Default

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



  #6   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Excel insits that I use absolute cell references

http://tutorialway.com/use-absolute-...ence-in-excel/
helpful site
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
COPY CELL WITH FILE REFERENCES IN EXCEL natelee78 Excel Discussion (Misc queries) 4 July 12th 05 02:15 PM
Copy cell format to cell on another worksht and update automatical kevinm Excel Worksheet Functions 21 May 19th 05 11:07 AM
Excel cell references to 2nd worksheet stopped working StardustMM Excel Worksheet Functions 1 February 11th 05 05:31 PM
Excel: how to formulate conditional cell references centraloffice Excel Worksheet Functions 1 December 10th 04 09:57 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 11:01 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"