Formula from a single cell to a merged cell
 

Hi All,

I have a sheet with raw data(single cells) and a cover sheet linked to the
data by formulas. My cover sheet has merged cells and when I try to insert a
formula into one of the merged cells(linking back to the single cells) and
then drag down to copy the formula I find the formula skips a row.

For example, merged cells A1 and A2(cover sheet) will pick up single cell
A1(raw data) but when I drag the formula down to merged cells A3 and A4 it
will pick up the data in single cell A3 instead of A2.

Formula from a single cell to a merged cell
 

Another good reason not to merge cells! <g

Try this entered in A1 of your cover sheet:

=INDEX(Sheet2!A:A,ROWS(A$1:A2)/2)

A1:A2 = Sheet2!A1
A3:A4 = Sheet2!A2
A5:A6 = Sheet2!A3
A7:A8 = Sheet2!A4
etc
etc


--
Biff
Microsoft Excel MVP


"Rodders" wrote in message
...
Hi All,

I have a sheet with raw data(single cells) and a cover sheet linked to the
data by formulas. My cover sheet has merged cells and when I try to insert
a
formula into one of the merged cells(linking back to the single cells) and
then drag down to copy the formula I find the formula skips a row.

For example, merged cells A1 and A2(cover sheet) will pick up single cell
A1(raw data) but when I drag the formula down to merged cells A3 and A4 it
will pick up the data in single cell A3 instead of A2.

Formula from a single cell to a merged cell
 

hi
this is a problem with merged cells. excel is doing what it's suppose to do.
even though you have the cells merged, excel is still seeing rows 1 through
4. you may not be able to use the drag method. better just to type the
formula in.

regards
FSt1

"Rodders" wrote:

Hi All,

I have a sheet with raw data(single cells) and a cover sheet linked to the
data by formulas. My cover sheet has merged cells and when I try to insert a
formula into one of the merged cells(linking back to the single cells) and
then drag down to copy the formula I find the formula skips a row.

For example, merged cells A1 and A2(cover sheet) will pick up single cell
A1(raw data) but when I drag the formula down to merged cells A3 and A4 it
will pick up the data in single cell A3 instead of A2.

Formula from a single cell to a merged cell
 

Thanks. You have answered my question.

"T. Valko" wrote:

Another good reason not to merge cells! <g

Try this entered in A1 of your cover sheet:

=INDEX(Sheet2!A:A,ROWS(A$1:A2)/2)

A1:A2 = Sheet2!A1
A3:A4 = Sheet2!A2
A5:A6 = Sheet2!A3
A7:A8 = Sheet2!A4
etc
etc


--
Biff
Microsoft Excel MVP


"Rodders" wrote in message
...
Hi All,

I have a sheet with raw data(single cells) and a cover sheet linked to the
data by formulas. My cover sheet has merged cells and when I try to insert
a
formula into one of the merged cells(linking back to the single cells) and
then drag down to copy the formula I find the formula skips a row.

For example, merged cells A1 and A2(cover sheet) will pick up single cell
A1(raw data) but when I drag the formula down to merged cells A3 and A4 it
will pick up the data in single cell A3 instead of A2.

Formula from a single cell to a merged cell
 

You're welcome!

--
Biff
Microsoft Excel MVP


"Rodders" wrote in message
...
Thanks. You have answered my question.

"T. Valko" wrote:

Another good reason not to merge cells! <g

Try this entered in A1 of your cover sheet:

=INDEX(Sheet2!A:A,ROWS(A$1:A2)/2)

A1:A2 = Sheet2!A1
A3:A4 = Sheet2!A2
A5:A6 = Sheet2!A3
A7:A8 = Sheet2!A4
etc
etc


--
Biff
Microsoft Excel MVP


"Rodders" wrote in message
...
Hi All,

I have a sheet with raw data(single cells) and a cover sheet linked to
the
data by formulas. My cover sheet has merged cells and when I try to
insert
a
formula into one of the merged cells(linking back to the single cells)
and
then drag down to copy the formula I find the formula skips a row.

For example, merged cells A1 and A2(cover sheet) will pick up single
cell
A1(raw data) but when I drag the formula down to merged cells A3 and A4
it
will pick up the data in single cell A3 instead of A2.