Relative reference in merged cell

[kaushik03]

Hi all,


It's been quite a long time I have not been able to visit the forum...really missing you all wonderful people here!!!


However, I have come back with a small problem.


The output I require is explained in the workbook uploaded here:


http://speedy.sh/yfvd5/Number-Pattern.xlsx


Layout:


A2 to A5 are merged, similarly A6 to A9. A1 contains a variable.


In Col B there are some values. Starting from A2, at every 4th row, there is a text called "TOTAL".


What I am trying to do here is:


Write a formula at C2 and drag it down so, right beside the cell containing the text "TOTAL", I get the concatenated value (at C5) of A1(ConstVar), A2(Ram) and B5(Total). Same way, C9 should give me ConstVarSamTotal. Rest of the cells in between should be blank.


Hope I am able to make the requirement clear.


Regards,

Kaushik

 

[shrivallabha]

In C2:

=IF(B2<>"Total","",$A$1&INDEX(A:A,ROWS($A$1:$A1)-2)&B2)


and copy down.

 

[Haseeb A]

Hello Kaushik,


If merged cells are not always 4 cells, here is one way:


C2, then copy down.


=IF(B2="Total",A$1&LOOKUP("zzzzz",A$2:A2)&B2,"")


Haseeb

 

[SirJB7]

@kaushik03

Hi!

So long without reading you, hope you're doing fine. Welcome back, buddy.

Regards!