Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Query content of merged cells
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, instead of C1, I putted OFFSET($A$1,1,INT((COLUMN(B1)-2)/4)*4+1) but it isn't always true. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Query content of merged cells
Use Row 2 instead of Row 1 to compute your offsets
from OFFSET($A$1,1,INT((COLUMN(B1)-2)/4)*4+1) to OFFSET($A$2,0,INT((COLUMN(B2)-2)/4)*4+1) "Lucio Menci" wrote: 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, instead of C1, I putted OFFSET($A$1,1,INT((COLUMN(B1)-2)/4)*4+1) but it isn't always true. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Query content of merged cells
It's easier than it could appair...
Thank you "Joel" wrote: Use Row 2 instead of Row 1 to compute your offsets from OFFSET($A$1,1,INT((COLUMN(B1)-2)/4)*4+1) to OFFSET($A$2,0,INT((COLUMN(B2)-2)/4)*4+1) "Lucio Menci" wrote: 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, instead of C1, I putted OFFSET($A$1,1,INT((COLUMN(B1)-2)/4)*4+1) but it isn't always true. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Query content of merged cells
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofit Merged cell Code is changing the format of my merged cells | Excel Discussion (Misc queries) | |||
How to maintain merged cell format when cutting the content away | Excel Worksheet Functions | |||
how do i link merged cells to a merged cell in another worksheet. | Excel Worksheet Functions | |||
excel cell content cuts off when merged to word | Excel Discussion (Misc queries) | |||
Sorting merged cellsHow do I sort merged cells not identically siz | Excel Worksheet Functions |