Refer to value

[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

[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

[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

"strikeuk" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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

[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

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 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[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
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]

> ='[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" <[email protected]> wrote in message
news:[email protected]...
>> ='[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
> ---

[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.
--
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