Reference A Cell Conditionally Using ADDRESS And INDIRECT Functions

[mahmoud-lee]

Is this formula can be converted in to write a vertical or horizontal ?


https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)
https://www.eileenslounge.com/viewtopic.php?p=312533#p312533 (https://www.eileenslounge.com/viewtopic.php?p=312533#p312533)
https://www.eileenslounge.com/viewtopic.php?f=44&t=40373&p=312499#p312499 (https://www.eileenslounge.com/viewtopic.php?f=44&t=40373&p=312499#p312499)
https://www.eileenslounge.com/viewtopic.php?p=311844#p311844 (https://www.eileenslounge.com/viewtopic.php?p=311844#p311844)
https://archive.org/download/wlsetup-all_201802/wlsetup-all.exe (https://archive.org/download/wlsetup-all_201802/wlsetup-all.exe)
https://www.eileenslounge.com/viewtopic.php?p=311826#p311826 (https://www.eileenslounge.com/viewtopic.php?p=311826#p311826)
https://www.eileenslounge.com/viewtopic.php?f=37&t=40261&p=311783#p311783 (https://www.eileenslounge.com/viewtopic.php?f=37&t=40261&p=311783#p311783)
https://www.eileenslounge.com/viewtopic.php?p=310916#p310916 (https://www.eileenslounge.com/viewtopic.php?p=310916#p310916)
https://www.eileenslounge.com/viewtopic.php?p=310720#p310720 (https://www.eileenslounge.com/viewtopic.php?p=310720#p310720)
https://www.eileenslounge.com/viewtopic.php?f=56&t=40034&p=310171#p310171 (https://www.eileenslounge.com/viewtopic.php?f=56&t=40034&p=310171#p310171)
https://www.eileenslounge.com/viewtopic.php?p=310110#p310110 (https://www.eileenslounge.com/viewtopic.php?p=310110#p310110)
https://www.eileenslounge.com/viewtopic.php?p=310024#p310024 (https://www.eileenslounge.com/viewtopic.php?p=310024#p310024)
https://www.eileenslounge.com/viewtopic.php?p=309121#p309121 (https://www.eileenslounge.com/viewtopic.php?p=309121#p309121)
https://www.eileenslounge.com/viewtopic.php?p=309101#p309101 (https://www.eileenslounge.com/viewtopic.php?p=309101#p309101)
https://www.eileenslounge.com/viewtopic.php?p=308945#p308945 (https://www.eileenslounge.com/viewtopic.php?p=308945#p308945)
https://www.eileenslounge.com/viewtopic.php?f=30&t=39858&p=308880#p308880 (https://www.eileenslounge.com/viewtopic.php?f=30&t=39858&p=308880#p308880)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[Rick Rothstein]

Is this what you are looking for...

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

[mahmoud-lee]

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

[mahmoud-lee]

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

[Rick Rothstein]

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]

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

[Rick Rothstein]

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]

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]

I want it like this

[Rick Rothstein]

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")

[Excel Fox]

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]

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]

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]

.. 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"))

[mahmoud-lee]

thanks all of you
Excellent solutions

[mahmoud-lee]

thanks all of you
Excellent solutions