Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Reference A Cell Conditionally Using ADDRESS And INDIRECT Functions

  1. #1

  2. #2
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Is this what you are looking for...

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

  3. #3
    Senior Member
    Join Date
    Mar 2013
    Posts
    146
    Rep Power
    0
    no I want both vertical and horizontal ( row&column ) In same formula
    Last edited by mahmoud-lee; 06-04-2013 at 08:19 AM.

  4. #4
    Senior Member
    Join Date
    Mar 2013
    Posts
    146
    Rep Power
    0
    I know that this strange request, but I want converted it to game characters

  5. #5
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by mahmoud-lee View Post
    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.

  6. #6
    Senior Member
    Join Date
    Mar 2013
    Posts
    146
    Rep Power
    0
    I know this is good, but you can combine ( row & column ) in same formula

  7. #7
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by mahmoud-lee View Post
    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")
    Last edited by Rick Rothstein; 06-04-2013 at 08:56 AM.

  8. #8
    Senior Member
    Join Date
    Mar 2013
    Posts
    146
    Rep Power
    0
    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
    Last edited by mahmoud-lee; 06-04-2013 at 09:40 AM.

  9. #9
    Senior Member
    Join Date
    Mar 2013
    Posts
    146
    Rep Power
    0
    I want it like this
    Attached Files Attached Files
    Last edited by mahmoud-lee; 06-04-2013 at 09:55 AM.

  10. #10
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by mahmoud-lee View Post
    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")

Similar Threads

  1. Replies: 2
    Last Post: 01-24-2013, 09:03 PM
  2. Indirect Address Column Formula help needed
    By trankim in forum Excel Help
    Replies: 4
    Last Post: 10-13-2012, 02:15 PM
  3. Replies: 1
    Last Post: 08-21-2012, 07:36 PM
  4. Get last Filled Cell address in a Range.
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 3
    Last Post: 03-24-2012, 01:08 AM
  5. Excel Macro Functions (GET.CELL)
    By Admin in forum Excel and VBA Tips and Tricks
    Replies: 1
    Last Post: 05-17-2011, 08:56 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •