default link type

  • Thread starter Thread starter Joanne
  • Start date Start date
J

Joanne

Using WinXP Pro and MSOffice 2003 Pro

Everytime I do a link in Excel, it does it as an absolute link, by
default I am guessing.
I really never use absolute linking, so I would like to know if there is
a spot in excel where I can change my default to relative links? I don't
see a place to accomplish this in the options section.

Thanks for the info
Joanne
 
Links between workbooks always go Absolute.

Links within a workbook go Relative.

I have never seen a method or option that would change this default behaviour.

I don't guess that was much help<g>


Gord Dibben MS Excel MVP
 
Hi Joanne,

First, what kind of links are we talking about, formulas or hyperlinks?

Second, it is not true that all links within a workbook are relative (here
I'm referring to formulas), it depends on how you do them:

1. If you type = and then click on another sheet and click a cell - the link
is relative.
2. If you select a cell you want to link to and do Copy, move to a new
location within the workbook and choose Edit, Paste Special, Paste Links -
the link is absolute, even if its in the same sheet.
3. If you reference a range name, for example =Budget - the reference is
almost always absolute.
4. When you are creating links between workbooks using all the methods
described above the links default to absolute, except if the range name has
been defined as relative, something that is very rarely done. However, if
you type = and click in another workbook and on a cell, if you press F4 one
or more times before you press Enter, you can convert the reference to
relative. Of course you can always do that after you enter the formula by
selecting it (the formula, not the cell) and pressing F4 as many times as
necessary.

Finally, you can convert absolute references $A$5 to relative for one or
more cells by using Find & Replace. Press Ctrl+H, and enter $ in the find
what box and nothing in the replace with box.

Cheers,
Shane
 
Well, it did put the question to rest!!
Thanks

Gord said:
Links between workbooks always go Absolute.

Links within a workbook go Relative.

I have never seen a method or option that would change this default behaviour.

I don't guess that was much help<g>


Gord Dibben MS Excel MVP
Click to expand...
 
This might be a little off topic, but I'll ask anyway.

I work on multiple workbooks. They all calculate a lot of functions and I am
forced to create multiple workbooks to do these calculations. Linking to
other workbooks basically displays the results of workbook1 and works from
there.

But...

I need to keep an overview of these files, so I store them in different
folders all within the same 'master' folder. So let's say: master folder is
FolderMaster, and this contains folderA, folderB, folderC. FolderA contains
workbookA1, workbookA2; folderB contains workbookB1, workbookC2 and finally
folderC contains workbookC1 and workbookC2.

Suppose that this hierarchy might change. Moving eg workbook A2 to folderB.
This would cause a #REF.

So I thought: organise your hierarchy in a 'master workbook' and store all
paths to the individual files in a worksheet. If I link to a specific folder
I do not directly link to the actual folder, but would use INDIRECT and grap
the filepath from the masterworkbook. If the path to a workbook change I do
not have to update all individual links, but only change the path in the
masterworkbook. Right?

But how is the big one here. How do you formulate you path to a file? It can
not be the entire file path since that's variable... (sometimes
'hd:User1:....'; sometimes 'HD:User2'...")
 
Back
Top