Reference A Cell Conditionally Using ADDRESS And INDIRECT Functions

**Excel Fox** · 06-04-2013, 12:34 PM

Try this and drag down and across

B2=IFERROR(MID(SUBSTITUTE(TRIM(IF(INDIRECT(ADDRESS (ROW(),1))<>"",$A$2,$B$1))," ","x"),IF(INDIRECT(ADDRESS(ROW(),1))<>"",COLUMN(A1 ),ROW(A1)),1),"")

**Admin** · 06-04-2013, 03:10 PM

Hi

@ EF

If I replace A2 with "eboard Regular", the result seems incorrect.

another option

=IF(ROWS(B$2:B2)>1,SUBSTITUTE(MID($B$1,ROWS(B$2:B2 )-1,1)," ","x"),SUBSTITUTE(MID($A$2,COLUMNS($B2:B2),1), " ","x"))

**Excel Fox** · 06-04-2013, 03:23 PM

Admin, could you indicate what's seeming incorrect? I thought that both the first characters of the two cells would be the same. That's why I've excluded that.

Also, the formula you suggested above leaves a trailing 'x' because there's a blank character in the end. A trim can be added

**Admin** · 06-04-2013, 03:42 PM

Originally Posted by Excel Fox

.. I thought that both the first characters of the two cells would be the same. That's why I've excluded that.

fair enough.

Also, the formula you suggested above leaves a trailing 'x' because there's a blank character in the end. A trim can be added

Thanks.

if both the first characters are same, you can use this as well.

=IF(ROWS(B$2:B2)>1,SUBSTITUTE(MID(TRIM($B$1),ROWS( B$2:B2),1)," ","x"),SUBSTITUTE(MID(TRIM($A$2),COLUMNS($B2:B2),1 )," ","x"))