Linking Merged Cells in Different Workbooks

[jeffc4442]

Hi. I'm using Excel 2003 SP2, fully patched, in Windows XP Pro SP2, fully
patched. I understand how to link Cell A1, Book1 to Cell B2, Book 2 so that
whatever information is entered into B2,Book 2 automatically populates A1,
Book 1. It works every time (choose A1, type =; choose B2, hit enter).

However, i'm trying to do the same thing when merged cells are involved.
Here, i've got one cell merged from A1 & A2. I want information from the
merged cell from B1 & B2 to populate to the A1/A2 merged cell. I follow the
method above, and it seems to get the formula right, but i always get an
error.

Well, let me restate somewhat...when i use just plain merged cells, it
works. In my situation, i'm using cells merged from A1/A2, but the rows are
not merged all the way down the spreadsheet; i.e., A1/A2 are merged, but B1 &
B2 are not merged and so on. so, some of the cells b/t Rows 1 & 2 are
merged, but other cells b/t Rows 1 & 2 are not merged.

can this be done? any workaround ideas? Thanks in advance!

 

[Jim Cone]

Merged cell(s) can only contain data in the top left cell of the merged area.
The formula ... "= B1 & B2" in cell A1 works.

--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


"jeffc4442"
<[email protected]>
wrote in message
Hi. I'm using Excel 2003 SP2, fully patched, in Windows XP Pro SP2, fully
patched. I understand how to link Cell A1, Book1 to Cell B2, Book 2 so that
whatever information is entered into B2,Book 2 automatically populates A1,
Book 1. It works every time (choose A1, type =; choose B2, hit enter).

However, i'm trying to do the same thing when merged cells are involved.
Here, i've got one cell merged from A1 & A2. I want information from the
merged cell from B1 & B2 to populate to the A1/A2 merged cell. I follow the
method above, and it seems to get the formula right, but i always get an
error.

Well, let me restate somewhat...when i use just plain merged cells, it
works. In my situation, i'm using cells merged from A1/A2, but the rows are
not merged all the way down the spreadsheet; i.e., A1/A2 are merged, but B1 &
B2 are not merged and so on. so, some of the cells b/t Rows 1 & 2 are
merged, but other cells b/t Rows 1 & 2 are not merged.

can this be done? any workaround ideas? Thanks in advance!

 

[jeffc4442]

So, do you mean if i unmerge the cells and make sure the information is in
the top left cell, then remerge them, it should work?

 

[Jim Cone]

You don't have to unmerge cells in order to enter data.
What ever you enter only goes in the top left cell.
If you merge several cells that have data in them, all of the data is lost
except for that in the top left cell. Play around with it on a blank sheet.

Most experienced Excel users tend to avoid merging cells
You can increase the row height and/or format adjacent cells to
"center across selection" to achieve a similar effect.
One problem they can create is ...
try sorting column B when you have some cells from Columns A and B merged.
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html



"jeffc4442" <[email protected]>
wrote in message
So, do you mean if i unmerge the cells and make sure the information is in
the top left cell, then remerge them, it should work?

 

[jeffc4442]

Okay. i must have misunderstood your first response. i see where you say
that = B1 & B2 in A1 works, but what about B1 & B2 in A1 & A2? that's what
i'm trying to get to happen.

i'm cool with learning how to avoid merging cells. i see what you mean
about the sorting problem. I don't know what "center across selection"
means, but i can guess.

If i don't use merged cells, can i do what i'm currently doing? i have 2
rows "together" (obviously) where some cells are merged (a1 and a2) but
others are not merged; e.g., b1 & b2. how can i achieve this without merging
cells? would i use the "center across selection," but use vertically
adjacent cells (a1 & a2)? even if that will work, how do i set up the
dependent worksheet/book so that it will be correctly populated? do i have
to make sure all the formatting in the dependent worksheet matches the source
worksheet?

thanks, Jim. i appreciate your taking the time to help me out. in the
meantime, i'll see if i can't answer these questions myself by messing around
with it. thanks.

jeffc4442

 

[Jim Cone]

Another way to look at it: if you merge A1 and A2 then A1 becomes
twice as large and A2 doesn't exist any longer.

Select several adjacent cells in a row.
Enter something in the first selected cell.
Go to the Format menu | Cells.
On the alignment tab, choose center across selection in the "horizontal" drop down..
Regards,
Jim Cone



"jeffc4442" <[email protected]>
wrote in message
Okay. i must have misunderstood your first response. i see where you say
that = B1 & B2 in A1 works, but what about B1 & B2 in A1 & A2? that's what
i'm trying to get to happen.

i'm cool with learning how to avoid merging cells. i see what you mean
about the sorting problem. I don't know what "center across selection"
means, but i can guess.

If i don't use merged cells, can i do what i'm currently doing? i have 2
rows "together" (obviously) where some cells are merged (a1 and a2) but
others are not merged; e.g., b1 & b2. how can i achieve this without merging
cells? would i use the "center across selection," but use vertically
adjacent cells (a1 & a2)? even if that will work, how do i set up the
dependent worksheet/book so that it will be correctly populated? do i have
to make sure all the formatting in the dependent worksheet matches the source
worksheet?

thanks, Jim. i appreciate your taking the time to help me out. in the
meantime, i'll see if i can't answer these questions myself by messing around
with it. thanks.

jeffc4442

 

[jeffc4442]

Jim:

I can make the center across selection work using 2 new worksheets, but i
can't get it to work in my existing worksheet. I even unmerged all of the
cells, then tried it, but the data just stayed in its original cell. i tried
this with the data in the upper left hand cell of the 2 row cells selected,
and also in the upper right. neither worked.

In my scenario, though, i don't have the cells from 2 different colums
merged. I have cells from the same column, but different rows. A1 & A2, not
A1 & B1. Not that this is really addressing my overall problem, but is there
vertical version of center across selected?

Also, back to the linking problem...in my existing documents, i can make the
link work when i use the same cell numbers (A21/A22 & A21/A22), but every
time i use sells whose references don't match i get the #value! error. It
seems like if it only works this way, it would defeat the whole purpose of
linking. So, i know i must be doing something wrong or have something set up
wrong.

thanks again, Jim.

jeffc4442

 

[jeffc4442]

Jim:

when i mouse over the error notation in the dependent worksheet, i get this
message: "A value used in the formula is of the wrong data type."

jeff