Referencing merged cells

Hello,

I would like to reference the text in a set of merged cells with a function. If I have merged cells A1&A2, and then I reference them with the formulas "=A1" and "=A2", only the "=A1" returns the contents of the merged cells. The "=A2" formula returns 0.

Is there a way to get the contents of the merged cells regardless of which one is referenced (i.e. from EITHER A1 or A2)?

Many thanks all,

Will

Originally Posted by willblatchley

Hello,

I would like to reference the text in a set of merged cells with a function. If I have merged cells A1&A2, and then I reference them with the formulas "=A1" and "=A2", only the "=A1" returns the contents of the merged cells. The "=A2" formula returns 0.

Is there a way to get the contents of the merged cells regardless of which one is referenced (i.e. from EITHER A1 or A2)?

Many thanks all,

Will

when you merge cells A1&A2 it becomes cell A1 only. likewise if you merge cells B1:B5, the name of merged cell will be B1 only. you can fetch data from merged cells by referencing to only first row and first col of merged cells.
e.g if you merge cells B1:B5 and this contains a values say "excel forum"
in anyother cell by putting just =B1 will fetch "excel forum" from merged cells.

hopefully this would help you understand
Regards.

Thanks for your reply. The problem is that I don't know what is the first cell of the merged cells in my function.

In other words, I want to get the text in cell R[-1]C, even if that cell is part of a set of merged cells. Is this possible?

Is there some function that returns the top cell of several merged cells? Or a function that tests to see if a cell is part of merged cells or not?

Thanks,

Will

what you really want to do and what is your data structure is yet unclear.
please provide some sample data with cell references, in which it resides.

Sorry I've been unclear. Perhaps this will help explain.

Take the following simple sheet. My data is in column A, and includes some merged cells. I place the formula INDIRECT("RC[-1]",0) in column B, and copy it down the sheet. I get the result as shown in column C, but I would like the result shown in column D!

Please Login or Register  to view this content.

I realise WHY this doesn't work (as Excel refers to the merged cells A3:A5 as just A3, and A4 & A5 don't essentially exist any more), but I would like to know if there's a way to get around it, and achieve the result in column D. Any help would be very much appreciated!

Thanks for looking,

Will

dear Sir,

please clear solutions as i am already facing this issue...

Paras