absolute and relative table nomenclature
Results 1 to 6 of 6

Thread: absolute and relative table nomenclature
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2009
    Location
    Missouri
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default absolute and relative table nomenclature

    I am using EXCEL 2010. If I use table nomenclature in a formula and copy it into the columns to the right, the reference is relative and thus, changes as I copy to each column. Is there a way to make the table nomenclature reference absolute?

    I am using the following formula (it works and I can live with it):

    =INDEX(Demo11[Birthdate],MATCH($E7,Demo2011$T$3:$T$98,0))

    However, I would like to use this formula if I could keep Demo11[student] absolute:

    =INDEX(Demo11[Birthdate],MATCH($E7,Demo11[Student],0))


    Thank you

  2. #2
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,623
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: absolute and relative table nomenclature

    Quote Originally Posted by BanMeThuot View Post
    I am using EXCEL 2010. If I use table nomenclature in a formula and copy it into the columns to the right, the reference is relative and thus, changes as I copy to each column. Is there a way to make the table nomenclature reference absolute?

    I am using the following formula (it works and I can live with it):

    =INDEX(Demo11[Birthdate],MATCH($E7,Demo2011$T$3:$T$98,0))

    However, I would like to use this formula if I could keep Demo11[student] absolute:

    =INDEX(Demo11[Birthdate],MATCH($E7,Demo11[Student],0))


    Thank you
    I hate structured referencing and this is one of the resaons why!

    Try this:

    =INDEX(Demo11[Birthdate],MATCH($E7,INDIRECT("Demo11[Student]"),0))
    .
    Biff
    Microsoft MVP - Excel

    Don't be afraid to use volatile functions or array formulas
    Tell us what version of Excel you're using
    KISS - Keep It Simple Stupid

  3. #3
    New Member
    Join Date
    Dec 2009
    Location
    Missouri
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: absolute and relative table nomenclature

    Thank you, sir!

    It works beautifully!

    BMT

  4. #4
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,623
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: absolute and relative table nomenclature

    Quote Originally Posted by BanMeThuot View Post
    Thank you, sir!

    It works beautifully!

    BMT
    You're welcome. Thanks for the feedback!
    .
    Biff
    Microsoft MVP - Excel

    Don't be afraid to use volatile functions or array formulas
    Tell us what version of Excel you're using
    KISS - Keep It Simple Stupid

  5. #5
    MrExcel MVP DonkeyOte's Avatar
    Join Date
    Sep 2002
    Location
    Suffolk, UK
    Posts
    9,118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: absolute and relative table nomenclature

    Quote Originally Posted by BanMeThuot View Post
    I am using EXCEL 2010. If I use table nomenclature in a formula and copy it into the columns to the right, the reference is relative and thus, changes as I copy to each column.
    The above is not entirely accurate.

    Table references are Relative when formulas are dragged (e.g. Fill Handle is used)
    Table references are Absolute when literally Copied (Ctrl + C etc)

    I suspect the above does not resolve your issue as you wish to mix the two (?), however, I thought I would add for sake of reference.
    Does my a$$ look big in this picture ?

  6. #6
    New Member
    Join Date
    Dec 2009
    Location
    Missouri
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: absolute and relative table nomenclature

    DonkeyOte,

    Thank you! I did, indeed, drag my formula to the right using the full handle. Thus, the Table Reference was Relative.

    As an EXCEL newbie I am struggling to learn the correct terminology. Thank you!

    BMT

Some videos you may like

User Tag List

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
  •