When referencing a cell it will generate every other cell

[DamYo]

Hello,

When I merge and try to drag that cell while referencing another cell, it would grab every other cell. So if i have 1, 2, 3, 4, 5,6 it would only give me 1,3, and 5. Is there a formula I can use ?

Thank you in advance.

 

[Leith Ross]

Hello DamYo,

Excel formulas do not like merged cells. When cells are merged, the reference to the merged cells is always the cell that is in the upper left hand side of the rectangle. From what you described, you had merged cells 1 and 2; 3 and 4; 5 and 6. Does that make sense?

 

[DamYo]

Thank you Leith,

I was hoping there was something out there. I've spent couple of days researching for a solution with no luck.

 

[Scott Huish]

I think this is what you mean. Assuming you are pulling values from row 1 starting at A1:

=INDEX(1:1,EVEN(COLUMN(A1))/2)

Copy across.

Row 2 are the merged cells in this example, even though it isn't displaying correctly.

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L
1	1	2	3	4	5	6
2	1		2		3		4		5		6
Sheet1

Cell Formulas
Range	Formula
A2	=INDEX(1:1,EVEN(COLUMN(A1))/2)
C2	=INDEX(1:1,EVEN(COLUMN(C1))/2)
E2	=INDEX(1:1,EVEN(COLUMN(E1))/2)
G2	=INDEX(1:1,EVEN(COLUMN(G1))/2)
I2	=INDEX(1:1,EVEN(COLUMN(I1))/2)
K2	=INDEX(1:1,EVEN(COLUMN(K1))/2)

 

[DamYo]

Thank you Scott,

Would you know if this applies to google sheets also ? or something similar?

 

[Scott Huish]

I don't use Google Sheets but I tried it and it seemed to work just fine.

 

[DamYo]

Hmmm...Interesting. I wasn't able to get it to work but I just got off of work and I am unable to give you more feedback.

Thank you for the help. Hopefully you could help me tomorrow.

 

[DamYo]

I don't use Google Sheets but I tried it and it seemed to work just fine.

THANK YOU SO MUCH SCOTT!!!! YOU'RE AWESOME and it worked. Cheers