Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Reference column by number

This is a discussion on Reference column by number within the Excel Questions forums, part of the Question Forums category; Ok, imagine Excel didn't have column letters, and you simply had x,y x= column, y=row. Now, I know the column ...

  1. #1

    Join Date
    Jul 2002
    Location
    Indiana
    Posts
    181

    Default

    Ok, imagine Excel didn't have column letters, and you simply had x,y
    x= column, y=row.
    Now, I know the column I want is column 7 (which is column G)and that I want the data from rows 3 thru 62.
    Normally I could do G3:G62
    But using the premise that I only know the number of the column, how can I do the same thing as G3:G62?

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default

    Howdy Roderick, welcome to the board (from a Hoosier Alum).

    Try using the cells functionality. Here's and example:


    Sub cls()
    MsgBox Range(Cells(3, 7), Cells(62, 7)).Address(False, False)
    End Sub


    The columns and rows are reversed, rowindex then column index numbers.

    _________________
    Cheers, Nate Oliver


    [ This Message was edited by: NateO on 2002-07-05 12:33 ]

  3. #3

    Join Date
    Jul 2002
    Location
    Indiana
    Posts
    181

    Default

    Ack! -- perhaps in an Excel function rather than VB code? --thanks

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default

    Ack!

    Well, I'm sure there's something better out there but here's an example:

    =SUM(INDIRECT(ADDRESS(3,7)):INDIRECT(ADDRESS(62,7)))

    There is a third paramter to address describing the reference style (e.g., absolute), it's not pertinent in the example above.

    Gotta get my head out of the VBE . That's twice in two days I've done that.

    Edit: You can replace the numbers w/ cell references.

    _________________
    Cheers, Nate Oliver


    [ This Message was edited by: NateO on 2002-07-05 12:49 ]

  5. #5
    Board Regular Barry Katcher's Avatar
    Join Date
    Feb 2002
    Location
    Dog Beach, Florida. Yeaahh!
    Posts
    4,055

    Default

    No VBA required. Click on Tools/Options/General and check the box for "R1C1 reference style". This will change your column headings from letters to numbers. Then, if you have, for example, numbers in rows 10 thru 13 in column 7, you can use the following formula to, for instance, find their sum:

    =SUM(r10c7:r13c7) where "r10" is row 10 and "c7" is column 7, etc.

    this is the equivilent of =SUM(G10:G13) in the normal "letter" column mode.
    Barry-

    Photo Restoration/Enhancement

    http://www.smiledogproductions.com
    click below for detour


  6. #6

    Join Date
    Jul 2002
    Location
    Indiana
    Posts
    181

    Default

    Great! almost there!!! -- one more question.
    I'm trying to access another sheet,
    Normally =Sheet2!G$3:G$62 would work just fine, How do I make it access using your code?

    thanks again! -- Hoosier are we!

  7. #7
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default

    Continuing with my bear of an example, perhaps Barry can expand on his, it could look like:

    =SUM(INDIRECT(ADDRESS(3,7,,,"sheet2")):INDIRECT(ADDRESS(62,7,,,"sheet2")))

  8. #8
    Board Regular Barry Katcher's Avatar
    Join Date
    Feb 2002
    Location
    Dog Beach, Florida. Yeaahh!
    Posts
    4,055

    Default

    Using my method, placing the following formula in cell A1 (or cell r1c1) in sheet 1 will give the sum of numbers in sheet 2, row 3/column 7 to row 61/column 7 (G3 to G61):

    =SUM(Sheet2!r3c7:r61c7)

    Incidentally, you don't even have to change the worksheet to display column headers as numbers. The references "G3" and "r3c7" are interchangeable and give same result no matter how screen is displayed. Changing column headings is only for ease in seeing which column is which when selecting r1c1 method.
    Barry-

    Photo Restoration/Enhancement

    http://www.smiledogproductions.com
    click below for detour


  9. #9

    Join Date
    Jul 2002
    Location
    Indiana
    Posts
    181

    Default

    Thanks guys, I used NateO's code and here is what it looks like totally implemented -- thanks again!!!
    ----
    =IF(ROUNDUP(INDEX(INDIRECT(ADDRESS(3,MATCH(I4,opspercalls_aban!A$2:K$2,0),,,"opspercalls_aban")):INDIRECT(ADDRESS(62,MATCH(I4,opspercalls_aban!A$2:K$2,0),,,"opspercalls_aban")),MATCH(IF(CEILING(calls!H3, 100)F$1, F$1,ROUNDUP(INDEX(INDIRECT(ADDRESS(3,MATCH(I4,opspercalls_aban!A$2:K$2,0),,,"opspercalls_aban")):INDIRECT(ADDRESS(62,MATCH(I4,opspercalls_aban!A$2:K$2,0),,,"opspercalls_aban")),MATCH(IF(CEILING(calls!H3, 100)

  10. #10
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,702

    Default

    You're welcome Roderick.

    Speaking of bears...So this is funcitonal eh? Glad you're on top of the rest.

Page 1 of 2 12 LastLast

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
  •  


DMCA.com