Reference column by number

[Legacy 5596]

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?

 

[NateO]

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

Try using the cells functionality. Here's and example:<pre>
Sub cls()
MsgBox Range(Cells(3, 7), Cells(62, 7)).Address(False, False)
End Sub</pre>

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

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>

This message was edited by NateO on 2002-07-05 12:33

 

[Legacy 5596]

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

 

[NateO]

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,<font size=+2><font color="red"> Nate<font color="blue"> Oliver</font></font></font>

This message was edited by NateO on 2002-07-05 12:49

 

[Barry Katcher]

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.

 

[Legacy 5596]

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!

 

[NateO]

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")))

 

[Barry Katcher]

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.

 

[Legacy 5596]

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)<calls!H3+50, CEILING(calls!H3,100), CEILING(calls!H3, 100)-100),opspercalls_aban!A$3:A$62)),0)>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)<calls!H3+50, CEILING(calls!H3,100), CEILING(calls!H3, 100)-100),opspercalls_aban!A$3:A$62)),0))

 

[NateO]

You're welcome Roderick.

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