Linking with equals sign doesn't always work

[Guest]

Trying to link a cell in one worksheet to a cell in another worksheet using
the equals sign. Sometimes it works, other times I get a #VALUE! Sometimes
it woks in one colum of a worksheet and not another. When it doesn't work, I
will sometimes get a 0 value instead of the #VALUE result.
The cell I'm linking to is a group of merged cells.
Any help

 

[Guest]

Trying to link a cell in one worksheet to a cell in another worksheet using
the equals sign. Sometimes it works, other times I get a #VALUE! Sometimes
it woks in one colum of a worksheet and not another. When it doesn't work, I
will sometimes get a 0 value instead of the #VALUE result.
The cell I'm linking to is a group of merged cells.
Any help

 

[Guest]

Just noticed that when the link does work it's always in the same column as
the data being linked. Does that mean you can't link data from one worksheet
to another without the link being in the same column?

 

[Biff]

Hi!

Post some examples of "links" that work and some of those that don't. It
helps when you're specific!

Biff

 

[Guest]

Sheet I'm linking to is Capital (3). Cell I'm trying to link to is a merged
cell, G3:N3.
when I link from column G in any other worksheet, the link works. If I try
to link from columns H through N, I get a "zero" result. From any other
column, I get a #VALUE! error.
I notice that in the fx window the reference is ='Capital (3)'!G3:N3. If I
remove the :N3 portion of the reference, the link works.
Recreated a portion of this project in a new workbook and the links work
fine and the reference includes the G3 but not the N3 portion.
Looked at the formatting of the cells and columns and couldn't come up with
anything.
Have a problem even with the links that work, that is, those that are in the
same column (G); this is a forms design project and if I have to insert or
delete a column, I lose the links because it puts the linked cells in a
different column again.
Would appreciate any help in figuring out why it's acting like it is. It
might be a quirk in Excel 2003 but probably it's something I did.
I just tried somehting else and it looks like i can get the links to work
using the "copy" and "paste link" icons on the formatting toolbar but it
means redoing everything. And I would still like to know why it isn't
working with the "equals" key.
Any help will be appreciated.

 

[Vasant Nanavati]

1. Avoid merged cells.

2. If G3:N3 is merged you just need a reference to G3. The ":N3" is
unnecessary and probably the cause of the problem.

 

[Guest]

Thanks for the input. Will use the format toolbar in future

 

[Biff]

If you want the appearance of merged cells without actually merging
them....works horizontally only

Format>Cells>Alignment

Under Text alignment>horizontal

Select Center across selection.

This gives the appearance of merged cells without all the "baggage".

Biff