Hey guys, does anyone know why i can't refer to a value from a merged
cell in another workbook? If it's not merged i could refer to it. Any
idea why?

Thanks


--
strikeuk
------------------------------------------------------------------------
strikeuk's Profile: http://www.excelforum.com/member.php...o&userid=34512
View this thread: http://www.excelforum.com/showthread...hreadid=542760

I find that if a formula refers to the top-left cell of a merged range
in a remote worksheet, the correct value is returned.

For example if you merge cells A1 through B2 of [doobie.xls]sheet1

The formula =[doobie.xls]sheet1!a1 will return the value in the merged
range.

The formulas =[doobie.xls]sheet1!a2 or The formula
=[doobie.xls]sheet1!b1

will return 0


--
CaptainQuattro
------------------------------------------------------------------------
CaptainQuattro's Profile: http://www.excelforum.com/member.php...o&userid=32763
View this thread: http://www.excelforum.com/showthread...hreadid=542760

Hi!

Works ok for me:

='C:\TV\[Book2.xls]Sheet1'!$A$1 (A1,A2,B1,B2 are merged)

How are you referring to it?

Biff

"strikeuk" wrote in
message ...

Hey guys, does anyone know why i can't refer to a value from a merged
cell in another workbook? If it's not merged i could refer to it. Any
idea why?

Thanks


--
strikeuk
------------------------------------------------------------------------
strikeuk's Profile:
http://www.excelforum.com/member.php...o&userid=34512
View this thread: http://www.excelforum.com/showthread...hreadid=542760

Hi,

I'm doing it by first keyying "=" in the formula bar and then clicking
on the referring cell. For example AG41 AND AH41 are merged so when i
refer to the cell the formula will appear as follows

='[Test.xls]Ken'!$AG$41:$AH$41

However if i manually change it to

='[Test.xls]Ken'!$AG$41

it works. Why is that?


--
strikeuk
------------------------------------------------------------------------
strikeuk's Profile: http://www.excelforum.com/member.php...o&userid=34512
View this thread: http://www.excelforum.com/showthread...hreadid=542760

Hi,

I'm doing it by first keyying "=" in the formula bar and then clicking
on the referring cell. For example AG41 AND AH41 are merged so when i
refer to the cell the formula will appear as follows

='[Test.xls]Ken'!$AG$41:$AH$41

However if i manually change it to

='[Test.xls]Ken'!$AG$41

it works. Why is that? I have around 50 cells to refer to and this will
take some time manually changing each one, any faster way of doing it?


--
strikeuk
------------------------------------------------------------------------
strikeuk's Profile: http://www.excelforum.com/member.php...o&userid=34512
View this thread: http://www.excelforum.com/showthread...hreadid=542760

I'm doing it by first keyying "=" in the formula bar and then clicking
on the referring cell. For example AG41 AND AH41 are merged so when i
refer to the cell the formula will appear as follows

='[Test.xls]Ken'!$AG$41:$AH$41

When I do exactly as you describe above the formula I get is: (AG41 AND AH41
are merged)

=[Book1.xls]Sheet1!$AG$41

CaptainQuattro explained about referring to the top left cell. I don't know
why you get:

='[Test.xls]Ken'!$AG$41:$AH$41

this will take some time manually changing each one, any faster way of
doing it?

You can try EditReplace

Select the range of cells with these formulas
Goto EditReplace
Find what: :*
Replace with: nothing, leave this blank
Replace All
Close

Biff

"strikeuk" wrote in
message ...

Hi,

I'm doing it by first keyying "=" in the formula bar and then clicking
on the referring cell. For example AG41 AND AH41 are merged so when i
refer to the cell the formula will appear as follows

='[Test.xls]Ken'!$AG$41:$AH$41

However if i manually change it to

='[Test.xls]Ken'!$AG$41

it works. Why is that? I have around 50 cells to refer to and this will
take some time manually changing each one, any faster way of doing it?


--
strikeuk
------------------------------------------------------------------------
strikeuk's Profile:
http://www.excelforum.com/member.php...o&userid=34512
View this thread: http://www.excelforum.com/showthread...hreadid=542760

='[Test.xls]Ken'!$AG$41 works. Why is that?
Think it's because this is a "normal" link formula, unlike the earlier

='[Test.xls]Ken'!$AG$41:$AH$41
which needs to be array-entered
(press CTRL+SHIFT+ENTER, instead of just pressing ENTER)

And we probably need the formula to be placed within
an equivalent 2 adjoining horiz cell range, eg: in A1:B1
to correctly return the link values from the 2 link cells (AG1 and AH1).

It'll still work if we array-enter it in a single cell, say in A1,
but then it'll return only the link value in the leftmost cell AG41.

Anyway, the above "mess" is one problem resulting from using / having merged
cells, which, going by past posts in the excel newsgroups, is known to cause
several downstream problems (It's best to avoid using merged cells)

I have around 50 cells to refer to and this will
take some time manually changing each one, any faster way of doing it?

Assuming your 50 cells are merged likewise contiguously down cols AG & AH,
i.e. with AG1:AH1 merged, AG2:AH2 merged, ... AG50:AH50 merged

In the starting cell, just change: ='[Test.xls]Ken'!$AG$41
to: ='[Test.xls]Ken'!AG41 (remove the $ signs)
then copy down 50 cells

(Think the $ signs are inserted by default when we link across books)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

='[Test.xls]Ken'!$AG$41:$AH$41
which needs to be array-entered
(press CTRL+SHIFT+ENTER, instead of just pressing ENTER

It'll still work if we array-enter it in a single cell, say in A1,
but then it'll return only the link value in the leftmost cell AG41.

The top left cell is the only cell that contains data so entering as an
array is pretty much useless.

Try this: =AH41

You'll get 0 because AH41 is empty.

Biff

"Max" wrote in message
...
='[Test.xls]Ken'!$AG$41 works. Why is that?
Think it's because this is a "normal" link formula, unlike the earlier

='[Test.xls]Ken'!$AG$41:$AH$41
which needs to be array-entered
(press CTRL+SHIFT+ENTER, instead of just pressing ENTER)

And we probably need the formula to be placed within
an equivalent 2 adjoining horiz cell range, eg: in A1:B1
to correctly return the link values from the 2 link cells (AG1 and AH1).

It'll still work if we array-enter it in a single cell, say in A1,
but then it'll return only the link value in the leftmost cell AG41.

Anyway, the above "mess" is one problem resulting from using / having
merged
cells, which, going by past posts in the excel newsgroups, is known to
cause
several downstream problems (It's best to avoid using merged cells)

I have around 50 cells to refer to and this will
take some time manually changing each one, any faster way of doing it?

Assuming your 50 cells are merged likewise contiguously down cols AG & AH,
i.e. with AG1:AH1 merged, AG2:AH2 merged, ... AG50:AH50 merged

In the starting cell, just change: ='[Test.xls]Ken'!$AG$41
to: ='[Test.xls]Ken'!AG41 (remove the $ signs)
then copy down 50 cells

(Think the $ signs are inserted by default when we link across books)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Biff,

Yes, of course, in this particular context.
Was just explaining it to the OP for completeness sake <g
I didn't say that he should do it, i.e. enter the default link array
produced by Excel in this instance.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Biff" wrote:
='[Test.xls]Ken'!$AG$41:$AH$41
which needs to be array-entered
(press CTRL+SHIFT+ENTER, instead of just pressing ENTER

It'll still work if we array-enter it in a single cell, say in A1,
but then it'll return only the link value in the leftmost cell AG41.

The top left cell is the only cell that contains data so entering as an
array is pretty much useless.

Try this: =AH41

You'll get 0 because AH41 is empty.

Biff