copy a column of single cells into a column of merged cells

C

clairejane_99

I am trying to copy a column of single cells on one worksheet into a
column of merged cells on an adjacent worksheet- does anyone have any
methods of achieving this? I tried formulas, but the referencing gets
messed up and in the merged cells it only displays every fourth entry
from the column of single cells. Any help would be appreciated.
Claire
 
D

David McRitchie

Hi Claire,
Possibly the fact that you have merged cells is
obscuring what you are actually trying to do, does it work
if the cells are not merged. I am assuming that your
cells are merged across and not down.

Try copying to cells that are not merged.
If you copy formulas the formulas will be adjusted to
where they are pasted to and will refer to the same page
that they are on as they will not refer back to the first page
unless you had sheet references in the formulas to begin with.

If you want the cells on the second sheet to refer back to
the first page and the formulas can be coped down by use of the
fill handle (and the merging is same amongst the cells on
the second page is merged across cells on one row per use.

K | Val | Formula
54 | 99 | =A2+A3
45 | 139 | =A3+A4
94 | 143 | =A4+A5
49 | 121 | =A5+A6
72 | 129 | =A6+A7
57 | 57 | =A7+A8

sheet 2
D2: =sheet1!B2
and fill down with fill handle

Create the formula in D2 by placing =
sign into cell D2 of sheet2 then click on cell B2 on sheet1
then hit Enter, which returnjs you to sheet2. Use fill handle
to copy down.

If you had cells in D and E merged across rows on the second
sheet the above works the same way. Remember a merged
cell is addressed by the upper left corner of the merged cell.

If that does not help you will have to be more specific about
how cells are merged, and what the formulas are from the
source and what you got and what you expected to see on
the second sheet.
 
S

SandyUK

Hi Claire

I have found that merged cells are more hassel than they are worth but
I understand that the appearance of centred text across cells looks
good on a sheet. I use an addin by Rob Bovey which achieves this
without merging so everything looks good but it makes referncing and
calculations a whole lot easier plus it has some other really useful
functions. The link to Rob's site is below and even if you don't use
the addin its worth a look as he has some very cool stuff on it.

http://www.appspro.com/Utilities/ExcelUtilities.htm

Regards

Adrian
 
C

CJ

Thanks to both Adrian and David. I actually got round the problem using
the LOOKUP function.
I had four rows merged and it was imporant to have four individual
cells runing to the left of the merged one.

Cheers,

Claire
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top