Use functions when cells are merged

[Amanda]

I have a worksheet with data on the first tab. Cells C2, C3, C4 and C5 are
merged cells and cells E2, E3, E4 and E5 are NOT merged but coinside with the
other cells in the respective rows. Then the next 4 cells are merged in
column C and they again go with column E which is never merged. I am trying
to get a count of items in Column E that have "Absent" in the column AND
Column C has "Allen". If I un-merge the cells then the data only goes into
the first cell and the others are left blank. I want it to assume that if
the cells are merged then all merged cells contain that data.
Please help! I worked on this for several hours.

[bj]

Most of us have been bit by the merged cell problems at one time or another
your best bet is to use a helper column where you make the cells = the value
you want to compare.
if it is always 4 cells and they are always adjacent it wont be hard to do
=c2 in four cells and then copy these four down as far as you need.

if the number of cells merged is not constant or if there isodd spacing it
becomes much more difficult

"Amanda" wrote:

> I have a worksheet with data on the first tab. Cells C2, C3, C4 and C5 are
> merged cells and cells E2, E3, E4 and E5 are NOT merged but coinside with the
> other cells in the respective rows. Then the next 4 cells are merged in
> column C and they again go with column E which is never merged. I am trying
> to get a count of items in Column E that have "Absent" in the column AND
> Column C has "Allen". If I un-merge the cells then the data only goes into
> the first cell and the others are left blank. I want it to assume that if
> the cells are merged then all merged cells contain that data.
> Please help! I worked on this for several hours.

[Amanda]

they are not always the same number of merged cells. I am importing the data
from another program so it automatically merges those cells. I appreciate
the help and if you know of another way that would also be appreciated. I
can get the calculations to work but only by the first cell in Column E that
relates to the merged group and then it goes down to the next group. It
doesn't look at the next cell in column E that relates to Column C

"bj" wrote:

> Most of us have been bit by the merged cell problems at one time or another
> your best bet is to use a helper column where you make the cells = the value
> you want to compare.
> if it is always 4 cells and they are always adjacent it wont be hard to do
> =c2 in four cells and then copy these four down as far as you need.
>
> if the number of cells merged is not constant or if there isodd spacing it
> becomes much more difficult
>
> "Amanda" wrote:
>
> > I have a worksheet with data on the first tab. Cells C2, C3, C4 and C5 are
> > merged cells and cells E2, E3, E4 and E5 are NOT merged but coinside with the
> > other cells in the respective rows. Then the next 4 cells are merged in
> > column C and they again go with column E which is never merged. I am trying
> > to get a count of items in Column E that have "Absent" in the column AND
> > Column C has "Allen". If I un-merge the cells then the data only goes into
> > the first cell and the others are left blank. I want it to assume that if
> > the cells are merged then all merged cells contain that data.
> > Please help! I worked on this for several hours.

[bj]

one way to do it is to use the helper column (H?)
merged cells in columnA?


in the first row with merged Cells (2?)
in H2enter
= A2
in H3 enter
=if(A3="",H3,A3)
and copy down to the bottom of your data
use this column as your name column.


"Amanda" wrote:

> they are not always the same number of merged cells. I am importing the data
> from another program so it automatically merges those cells. I appreciate
> the help and if you know of another way that would also be appreciated. I
> can get the calculations to work but only by the first cell in Column E that
> relates to the merged group and then it goes down to the next group. It
> doesn't look at the next cell in column E that relates to Column C
>
> "bj" wrote:
>
> > Most of us have been bit by the merged cell problems at one time or another
> > your best bet is to use a helper column where you make the cells = the value
> > you want to compare.
> > if it is always 4 cells and they are always adjacent it wont be hard to do
> > =c2 in four cells and then copy these four down as far as you need.
> >
> > if the number of cells merged is not constant or if there isodd spacing it
> > becomes much more difficult
> >
> > "Amanda" wrote:
> >
> > > I have a worksheet with data on the first tab. Cells C2, C3, C4 and C5 are
> > > merged cells and cells E2, E3, E4 and E5 are NOT merged but coinside with the
> > > other cells in the respective rows. Then the next 4 cells are merged in
> > > column C and they again go with column E which is never merged. I am trying
> > > to get a count of items in Column E that have "Absent" in the column AND
> > > Column C has "Allen". If I un-merge the cells then the data only goes into
> > > the first cell and the others are left blank. I want it to assume that if
> > > the cells are merged then all merged cells contain that data.
> > > Please help! I worked on this for several hours.