# Query content of merged cells

L

#### Lucio Menci

Hi,

I have cells B1:E1 merged, and F1:K1 merged and so on.
In the rest of my sheet I have formulas that have to refer to the content of
first row.
If the formula refers to B1, it runs, but if it refers to C1 or D1, it
doesn't run.

In the past, I had all merged range with the same number of cells, then,
OFFSET(\$A\$1,1,INT((COLUMN(B1)-2)/4)*4+1)
but it isn't always true.

from
OFFSET(\$A\$1,1,INT((COLUMN(B1)-2)/4)*4+1)

to
OFFSET(\$A\$2,0,INT((COLUMN(B2)-2)/4)*4+1)

It's easier than it could appair...
Thank you

Joel said:

from
OFFSET(\$A\$1,1,INT((COLUMN(B1)-2)/4)*4+1)

to
OFFSET(\$A\$2,0,INT((COLUMN(B2)-2)/4)*4+1)

Hello Lucio,

If you want your type of formula I suggest to take
=OFFSET(\$A\$1,0,INT((COLUMN(B\$1)-2)/4)*4+1)

But if you want to have a non-volatile function (that is: a function
which does not recalculate each and every time you press F9):
=INDEX(\$1:\$1,0,FLOOR((COLUMN(B\$1)-2),4)+2)

OFFSET is volatile, INDEX is not. BTW: FLOOR does exactly what you
introduce with INT(x/4)^4.

Regards,
Bernd