Copy Paste Issue Merge Cell & Paste Values other Column

Hi All,
hope you are good, first i need a help to copy merge cell value and paste in another location column H without spaces ( merge cell ).
second in column C i want to paste column A names to Column C merge cell. i select Column C ( c1 to c18 ) range then use formula =INDEX($A$1:$A$5,COUNTA(C$1:C1)) Ctrl +Enter result is ZERO.

file is attached

Thanks

Originally Posted by avk

I don't understand about merged cell. What is purpose of merging of data. Merge practice not a good sign.

Thanks avk for the reply, actually i know merge is not a good practice, but its a system generated report export to excel, i want alpanumaric code paste to this range, there are two diffrent report not code are match to each other, and second problem date is not also match to each other.
So now i have to replace first report codes to other.
One report is with merge cell. Is there any easy solution to paste this?

Thanks

Originally Posted by Eastw00d

Hi,
for the second question: if the cells contain a cell reference, you could use something like this:

=INDIRECT(C1)

or you can add this to my earlier formula
Cheers
Erwin

Hi Eastw00d,
Thanks for the reply, the first formula works perfect to unmerge cell column H, but the second i apply did not work. can you please more clarify it =indirect(c1). do i have to create name range by name manager box? . i use =indirect(A1) of cell C1 but did not work.

Thanks

in this case when i change a value of cell c1 to c18 replace A1 to TT01 and A4 to YYM1 the indirect formula result is incorrect column H but in column K without INDIRECT formula is giving OK result. can you tell me how INDIRECT is not giving perfect result?

file is attached.

Thanks Eastw00d,
its really perfect, no doubt like a cheer, thanks a lot, kindly one more thing tell me i would like to sum value column E against column C merge cell A1. please see the attached file.
column K i use your formula without INDIRECT to find actual value of cell C1 then apply to sum value =SUM(OFFSET(INDEX($C$1:$C$18,MATCH(K1,$C$1:$C$18,0)),0,2,4,1)). in this formula last one row height is not fix so i put manually type 4,3,2,5,4 when i drag the formula. kindly tell me how to this by formula height AUTO.

kindly consider to this, i would be very thank full for you.

Thanks

Thanks Eastw00d,
Ok i create a new thread for this.
thanks a lot.

Originally Posted by AliGW

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

my last question exactly related to this Thread INDIRECT function is not working when i change values in Column C. i started a new thread for OFFSET function Issue.