+ Reply to Thread
Results 1 to 10 of 10

Excel Formula to return column number of a cell referenced in another cell

  1. #1
    Forum Contributor
    Join Date
    10-22-2012
    Location
    London, UK
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    162

    Excel Formula to return column number of a cell referenced in another cell

    Hi,

    I know this should be simple but just can't seem to get the formula quite right and google search isn't helping.

    I have a cell (say Sheet2 A1) that references a cell in another sheet (same workbook), something like:

    =Sheet1!C4

    It pulls back the text from that cell, the heading for a table, say "Size".

    In Sheet2 A2, the cell next to the one with the simple reference formula, I'd like to display the column number for the referenced cell (Sheet1 C4) not the cell containing the reference (Sheet2 A1).

    I know you can use indirect to do this when referencing cells on the same sheet, but can't seem to get it to work across worksheets.
    I'm guessing I need some variation of:

    =Column(Indirect(A1))

    Should be simple but I can't get it to work.

    Thanks for your help.

    TC

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Excel Formula to return column number of a cell referenced in another cell

    Maybe this?????

    =CELL("col",Sheet1!C4)
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Registered User
    Join Date
    11-09-2018
    Location
    USA
    MS-Off Ver
    365
    Posts
    59

    Re: Excel Formula to return column number of a cell referenced in another cell

    Also, =COLUMN(Sheet1!C4) will work.

  4. #4
    Forum Contributor
    Join Date
    10-22-2012
    Location
    London, UK
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    162

    Re: Excel Formula to return column number of a cell referenced in another cell

    Thanks for the replies - however I don't want to reference Sheet1 C4 directly - I want to get the column by using the reference located in Sheet2 A1. The reference may change but I want to always know the column linked to that reference without having to update each and every cell formula.

    Hope that makes sense.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Excel Formula to return column number of a cell referenced in another cell

    Can you explain why you want to do this? Can you attach a sample worksheet to illustrate the reason for the request?

  6. #6
    Forum Contributor
    Join Date
    10-22-2012
    Location
    London, UK
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    162

    Re: Excel Formula to return column number of a cell referenced in another cell

    I want to list out various properties for referenced cells to be used later on. By all referring to the one reference then there only needs to be one cell updated should things change. It gives the user visibility of what's going on.

    I can't upload a workbook as I'm at work at the moment and our security setting won't allow it.

    Like I said - I know it's possible on the same sheet using INDIRECT, but I wanted to have all my workings on another sheet to keep it tidier.

    If you paste the following into Sheet1 A1:
    B2 1.333 1
    C3 45 8
    George 10 30


    Then below it (same sheet) paste the formula:
    =COLUMN(INDIRECT(A2))

    Then you'll get a result of 3 - column 3. I want to be able to do this but from another worksheet however INDIRECT doesn't seem to work across sheets

    (Just =INDIRECT(A2) gives the value of C3, so 30)

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Excel Formula to return column number of a cell referenced in another cell

    Out walking.... RU Ok with a VBA solution, assuming that yiur profile is correct and that you DO NOT have Excel 2013 or later....

  8. #8
    Forum Contributor
    Join Date
    10-22-2012
    Location
    London, UK
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    162

    Re: Excel Formula to return column number of a cell referenced in another cell

    Thanks Glenn - I know how to do it in VBA, but wanted a 'more basic' formula solution for this so I can hand it over and let the users maintain.

    Really didn't think it would be so awkward to do as a formula - but you never can tell with Excel!

    Profile is correct - only up to 2010.

    Enjoy your walk!

  9. #9
    Forum Contributor
    Join Date
    10-30-2003
    Location
    Singapore
    MS-Off Ver
    Excel 2019
    Posts
    197

    Re: Excel Formula to return column number of a cell referenced in another cell

    Maybe............

    1] In Sheet A1, enter : =Sheet1!C4

    2] Select A1 >> Text to Columns >> Next >> Next >> In step 3 of 3, "Column data format" choose: "Text" >> Finish

    then, in B1 enter formula

    =COLUMN(INDIRECT(SUBSTITUTE(A1,"=","")))

    Or,

    If you have Excel 2016, direct enter the formula :

    =COLUMN(INDIRECT(SUBSTITUTE(FORMULATEXT(A1),"=","")))

    Regards
    Bosco

  10. #10
    Forum Contributor
    Join Date
    10-22-2012
    Location
    London, UK
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    162

    Re: Excel Formula to return column number of a cell referenced in another cell

    Thanks Bosco - the first example does work but does kind of stop it being dynamic and not as obvious to the user what's going on.
    I only have 2010 so can't use the second example.

    Looks like I'll have to use VBA for this instead.

    I'll leave this post open though in case someone else has any ideas.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. function CELL() to return the formula in the referenced cell
    By Streep in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 06:05 PM
  2. function CELL() to return the formula in the referenced cell
    By RagDyer in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 09-06-2005, 05:05 PM
  3. function CELL() to return the formula in the referenced cell
    By Streep in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 PM
  4. function CELL() to return the formula in the referenced cell
    By RagDyer in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-06-2005, 09:05 AM
  5. [SOLVED] function CELL() to return the formula in the referenced cell
    By RagDyer in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-06-2005, 06:05 AM
  6. [SOLVED] function CELL() to return the formula in the referenced cell
    By RagDyer in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-06-2005, 04:05 AM
  7. function CELL() to return the formula in the referenced cell
    By Streep in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 02:05 AM
  8. [SOLVED] function CELL() to return the formula in the referenced cell
    By Streep in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1