Get value by reference from unmerged cell to merged cell byy formulae

[Krishna Kinker Singh]

Hi anyone can help to get value from unmerged cell to merged cell, sheet is attached for the reference, like I want to Unit in merged cell in 2nd sheet from 1st sheet.

 

[Somendra Misra]

Hi Krishna,

Welcome to Chandoo.org forum.

Can you specify your problem in more details.

Regards,

 

[Krishna Kinker Singh]

Hi Somendra ji,

I am attaching another sheet, I have sheet 2 in which I wnat to get value of sheet 1 which is Unit No.(Unit1,Unit2,Unit3,Unit4), due to cell of sheet 2 is merged so it don't come, like after Unit1, it comes Unit 4,

Becuase dragging in merged cell not working so what is the solution for that, I think this is clear to you now,

Thanks,

Krishna

 

[bobhc]

Good day Krishna and welcome to the forum, if you have not already done so, please read this link it will help you in the use of the forum.

http://chandoo.org/forum/threads/new-users-please-read.294/

You have already found out the limitations of merged cells, please read on:-

Avoid merging cells


Merged cells can help you arrange values in a meaningful way, but they come with problems -- numerous problems, big problems.


For instance, Excel won't apply column formats to a merged cell unless you select all the columns that comprise the merge.


In addition, not all cell formats, stick once you emerge a cell.


You can't sort a column with merged cells.


You can't even select a single-column range if there's a merged cell in it -- go ahead, try!, the whole column will become merged, not good.


You cannot put a filter on it. The problem is the filter is completely useless because the filter will groan with the "merged cells need to be identically sized." Warning, which in English means you have to make each group of merged cells the same size as the largest group. And you have to find them all!


Merging cells in columns and rows could lead to data loss, bad thing.


Formulas and Functions that refer to merged cells will not work, bad thing.


Don't hesitate to use merged cells if you really need them (you don’t), but they will limit what you can do to the cells and even the columns involved.


Center Across Selection is a far better alternative to merging.


To apply this format, select the cells you want to appear merged and then launch the Alignment group dialog, Ctrl + 1, and click the Alignment tab. Center Across Selection is in the Horizontal drop-down.


You will get the desired look you want but without the merged cell's problems.


.

 

[Krishna Kinker Singh]

It is done , Thanku