Results 1 to 5 of 5

Thread: Get Cell Reference From Cell Address ?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,351
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    3 Thread(s)

    Default Get Cell Reference From Cell Address ?

    Hi dear forum members,

    Is there a formula that would take a cell address and return the corresponding cell reference so I can then use this reference as an argument in other fonctions such as INDEX etc... ?

    Looking for a Non-UDF solution.

    Regards.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  2. #2
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,089
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Get Cell Reference From Cell Address ?

    Hi Jaafar,

    Take a look at INDIRECT() - for example.

    Excel 2013/2016
    ABC
    1AA1:A100C
    2B
    3C
    4D
    5E
    6F

    Sheet1



    Worksheet Formulas
    CellFormula
    C1=INDEX(INDIRECT(B1),3)

    [code]your code[/code]

  3. #3
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,351
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Get Cell Reference From Cell Address ?

    Quote Originally Posted by FormR View Post
    Hi Jaafar,

    Take a look at INDIRECT() - for example.
    Thanks for the quick response FormR,

    The INDIRECT function takes a cell reference not a cell address.

    I need to pass a literal address string something like "B1".
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

  4. #4
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,089
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Get Cell Reference From Cell Address ?

    Quote Originally Posted by Jaafar Tribak View Post
    The INDIRECT function takes a cell reference not a cell address
    Hi Jaafar, no - it takes a string and returns a range reference.

    In my example I put the string in a cell, but you don't need to do that, for example.

    Excel 2013/2016
    AB
    1AC
    2B
    3C
    4D
    5E
    6F

    Sheet1



    Worksheet Formulas
    CellFormula
    B1=INDEX(INDIRECT("A1:A100"),3)

    [code]your code[/code]

  5. #5
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    7,351
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Get Cell Reference From Cell Address ?

    You are right.

    The cell reference resolves to a string in the end... I am trying to incorporate the INDIRECT(), ADDRESS() AND CELL("address") functions into a larger formula... I'll get back if I get stuck.

    Thank you very much for your help.
    Office/Excel 2010 64Bits -- Win10 64Bits

    Common sense is not so common.


    http://photo-larache.blogspot.com/

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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