1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Simple Excel problem

Discussion in 'The Lounge' started by NARAYANK991, Jan 12, 2018.

  1. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,002
    Hi ,

    In a new worksheet , how do you go to a remote cell such as ZZ100000 , without typing this address in the Goto Reference box ?

    Narayan
    ThrottleWorks likes this.
  2. ThrottleWorks

    ThrottleWorks Excel Ninja

    Messages:
    1,767
    Hi @NARAYANK991 sir, is it a puzzle, I thought for a while.
    But could not think anything simpler than Control + G.

    Hyperlink ? But that would be more complex than reference box.

    Have a nice day ahead.
  3. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,002
    Hi ,

    CTRL G may be , but you are not supposed to enter the cell address in the Reference box.

    Narayan
    ThrottleWorks likes this.
  4. GraH - Guido

    GraH - Guido Active Member

    Messages:
    117
    You use a very large screen? :DD
    But you may not type the cell address anywhere. Not in the name box neither? (That field besides the formula bar)
    Like this I mean: upload_2018-1-12_17-31-52.png
    NARAYANK991 likes this.
  5. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,002
    Hi ,

    Not in the name box.

    Obviously it will have to be typed in somewhere ; the question is where can it be typed so that it can be used.

    Narayan
    ThrottleWorks likes this.
  6. GraH - Guido

    GraH - Guido Active Member

    Messages:
    117
    You are quite the riddle man, Naranyan... Intriguing it is.
  7. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    410
    Hi, Narayank!

    Some options:
    1. In name box (with A1 selected):
    R[99999]C[701] and hit Enter

    2. In name box (or Reference box), type:
    INDEX(A:ZZ,100000,702) or
    OFFSET(A1,99999,701) and hit Enter

    3. Alt + F11, Ctrl + G (Inmediate Window), put:
    [ZZ100000].Select and hit Enter

    Blessings!
  8. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,002
    Hi John ,

    I will go with #2.

    I never knew that we could enter formulae in the GoTo Reference box.

    My trial was by entering ZZ100000 in any worksheet cell , say A1 , and then using a formula :

    =INDIRECT(A1)

    in the CTRL G Reference box.

    This also works if we create a named range GoToRef and assign it the text string ZZ100000 , and then use a formula :

    =INDIRECT(GoToRef)

    in the CTRL G Reference box.

    Narayan
    Thomas Kuriakose likes this.
  9. GraH - Guido

    GraH - Guido Active Member

    Messages:
    117
    Hi John,
    Learning some new tricks here. Just wondering: you happen to know that ZZ is column 702?
  10. GraH - Guido

    GraH - Guido Active Member

    Messages:
    117
    Rather indirect solutions wouldn't you say, Naranyan?
  11. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    410
    Just put in any cell this:
    =COLUMN(ZZ1)

    Blessings!
    Thomas Kuriakose likes this.
  12. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,002
    Hi ,

    I wouldn't call it a solution ; what interested me was the fact that we could enter a formula in the GoTo Reference box , something that I did not know.

    I wanted to pass on this learning.

    Narayan
    Khalid NGO and Thomas Kuriakose like this.
  13. GraH - Guido

    GraH - Guido Active Member

    Messages:
    117
    Yes I knew about this. ;)
    Obviously... I just had a hard time figuring out what you were after with your challenge. But I dig it.
    I expected something more spectacular, that's all. But, like I said to John, did learn a new trick.:)

Share This Page