cannot use formula to link to another sheet

R

Robindotadams

Everytime I create a link in one sheet of excel to another sheet, it
overwrites the address with the sheet I am currently in (creating a circular
reference most of the time)... I think it's either an excel bug (never had
this before in 10 or so years of using excel) or a setting I've not seen
before... help please
 
G

Gord Dibben

Which version of Excel and how are you creating the link?

Typing the link in or pointing to the other sheet?

What would be a typical formula that exhibits this behaviour?


Gord Dibben MS Excel MVP
 
R

Robindotadams

Excel 2003

If I have 2 sheets, called "sheet1" and "sheet2".

In Cell A1 of sheet1 I type "+" and then use the mouse to click on sheet2
tab and cell B1 then "enter", the formula appears in sheet 2 cell A1, not
where I started it (Sheet1:A1). If I type the full formula in it works, but
I can't "select a cell on a different sheet with the mouse" as it then moves
the entire formula to that sheet!!!
 
G

Gord Dibben

=Sheet2!B1 manually entered in Sheet1!A1 works OK but when you type an = sign in
Sheet1A1 then point to Sheet2!B1 it does not.

Sheet2!A1 receives the formula and Sheet1!A1 is blank?

I cannot replicate this but perhaps another reader will have an idea.

I tried changing Transition options to Lotus mode and still could not replicate.


Gord
 
D

Dave Peterson

I couldn't duplicate it either.

But another option that may work for you until you determine the solution....

select the "sending" cell
edit|copy
select the "receiving" cell
edit|paste special|paste link
 
Top