A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Discussion (Misc queries)
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Excel insits that I use absolute cell references



 
 
Thread Tools Display Modes
  #1  
Old September 9th 05, 10:30 AM
Espen
external usenet poster
 
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.
Ads
  #2  
Old September 9th 05, 02:00 PM
Dave Peterson
external usenet poster
 
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  
Old September 9th 05, 02:22 PM
Espen
external usenet poster
 
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  
Old September 9th 05, 02:46 PM
Dave Peterson
external usenet poster
 
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  
Old October 6th 05, 10:19 AM
Espen
external usenet poster
 
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  
Old October 6th 05, 02:10 PM
Dave Peterson
external usenet poster
 
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  
Old January 5th 17, 06:47 AM 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
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 02:11 PM.


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