Reference A Cell Conditionally Using ADDRESS And INDIRECT Functions

**mahmoud-lee** · 06-04-2013, 08:07 AM

**Rick Rothstein** · 06-04-2013, 08:11 AM

Is this what you are looking for...

=SUBSTITUTE(MID(A$3,ROW(A1),1)," ","x")

**mahmoud-lee** · 06-04-2013, 08:14 AM

no I want both vertical and horizontal ( row&column ) In same formula

**mahmoud-lee** · 06-04-2013, 08:27 AM

I know that this strange request, but I want converted it to game characters

**Rick Rothstein** · 06-04-2013, 08:37 AM

Originally Posted by mahmoud-lee

no I want both vertical and horizontal ( row&column ) In same formula

If I understand your question correctly, I do not see how that could be possible. When you drag (copy) a formula down or across, the formula does not know anything about the cell where the formula was copied before it (Excel knows, that is why it can change the addresses as the formula moves from cell-to-cell, but the formula itself has no knowledge of it; hence, there would be know way for the formula to know to use ROW instead of COLUMN or vice-versa.

**mahmoud-lee** · 06-04-2013, 08:43 AM

I know this is good, but you can combine ( row & column ) in same formula

**Rick Rothstein** · 06-04-2013, 08:53 AM

Originally Posted by mahmoud-lee

I know this is good, but you can combine ( row & column ) in same formula

Oh, I think I understand what you want now... if I am right, this won't be too flexible (good for only one text cell) as it requires an absolute reference to the cell with the text. Give this formula a try and see if it does what you want...

=SUBSTITUTE(MID($A$1,ROW(A1)+COLUMN(A1)-1,1)," ","x")

**mahmoud-lee** · 06-04-2013, 09:20 AM

This is not exactly what I want
I know this is difficult, but I want if I wrote someone in a1(Separating characters in a cell ) is the same thing if I wrote another person in b2, for example
I'm very sorry I know I am tired you with me

**mahmoud-lee** · 06-04-2013, 09:41 AM

I want it like this

**Rick Rothstein** · 06-04-2013, 10:01 AM

Originally Posted by mahmoud-lee

I want it like this

You will not be able to do that with a single formula... you will need two separate formulas, but I do not see why that should be a problem. Put this formula in cell B2 and copy it across...

=SUBSTITUTE(MID($A$2,COLUMN(A1),1)," ","x")

Put this formula in cell B3 and copy it down...

=SUBSTITUTE(MID($B$1,ROW(A1),1)," ","x")