Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: two step reference

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    Hi,

    I have multiple named columns in a worksheet(a, b,c). I want to use data from either of these columns to create new data. Is there anyway I can put the name of the column I want to use in a cell and refer to the cell's content to identify the column i want to use? e.g i want to use data from column a, so I put some cell $C1 = a. And in a formula, I want to be able to get max(column name referred to in $C1).

    i tried CELL, defined cells etc. Didnt work!

    anybody know how to do this?

    thx

    dpk

  2. #2
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =MAX(indirect($C1))

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,308
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    G'day,

    INDIRECT will do it (if I understand you correctly). So say you have "a" typed into C1:

    =MAX(INDIRECT(C1&":"&C1))

    Hope that helps,
    Adam

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,308
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ahh,

    didn't catch that you defined a range in column A with "a".

    Evidently I need more caffeine.
    Adam

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,637
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-05-01 10:37, Steve Hartman wrote:
    =MAX(indirect($C1))
    Steve,

    That won't work when C1 just houses "A".

    Better:

    =MAX(INDIRECT(C1&":"&C1))

    I admit the purpose such an indirect reference could possibly serve escapes me.

    Aladin

  6. #6
    Board Regular Steve Hartman's Avatar
    Join Date
    Feb 2002
    Location
    Houston,Texas
    Posts
    417
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin,
    It should work since he said he had "named columns". I took that to mean a named range. If that assumption is incorrect, then you are quite right.

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    indirect worked.

    the purpose is to use data from a particular column, if i had best/worst/typical case parameters, i wanted to be able to decide based on a value in a cell (= bc/wc/tc), the parameter type i wanted to use.

    thx.

    dpk

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
  •