-
1 Attachment(s)
Reference A Cell Conditionally Using ADDRESS And INDIRECT Functions
-
Is this what you are looking for...
=SUBSTITUTE(MID(A$3,ROW(A1),1)," ","x")
-
no I want both vertical and horizontal ( row&column ) In same formula
-
I know that this strange request, but I want converted it to game characters
-
Quote:
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.
-
I know this is good, but you can combine ( row & column ) in same formula
-
Quote:
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")
-
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
-
1 Attachment(s)
-
Quote:
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")
-
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),"")
-
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"))
-
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
-
Quote:
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.
Quote:
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"))
-
thanks all of you
Excellent solutions
-
thanks all of you
Excellent solutions
