Refer to value

S

strikeuk

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
 
C

CaptainQuattro

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
 
B

Biff

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
 
S

strikeuk

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?
 
S

strikeuk

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?
 
B

Biff

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
Click to expand...

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?
Click to expand...

You can try Edit>Replace

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

Biff

strikeuk said:
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?
Click to expand...
 
M

Max

='[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
Click to expand...
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?
Click to expand...

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)
 
B

Biff

='[Test.xls]Ken'!$AG$41:$AH$41
which needs to be array-entered
(press CTRL+SHIFT+ENTER, instead of just pressing ENTER
Click to expand...
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.
Click to expand...

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 said:
='[Test.xls]Ken'!$AG$41 works. Why is that?
Click to expand...
Think it's because this is a "normal" link formula, unlike the earlier
='[Test.xls]Ken'!$AG$41:$AH$41
Click to expand...
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?
Click to expand...

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)
Click to expand...
 
M

Max

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.

Biff said:
='[Test.xls]Ken'!$AG$41:$AH$41
which needs to be array-entered
(press CTRL+SHIFT+ENTER, instead of just pressing ENTER
Click to expand...
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.
Click to expand...

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
Click to expand...
 
Top